Расчет зарплаты в sql-сервере

Привет, я использую следующую инструкцию Sql для расчета зарплаты

DECLARE @startDate DATETIME, @endDate DATETIME, @currentDate DATETIME, @currentDay INT, @PerDaycount INT, @Monthcount INT DECLARE @currentMonth INT, @lastDayOfStartMonth INT CREATE TABLE #VacationDays ([Month] VARCHAR(10), [DaysSpent] INT,[MonthDays] VARCHAR(10),[PerdayAmt] decimal(8,2),[TotalAmt] decimal(8,2)) DECLARE @Salary decimal(8,0) SET @Salary = 8000 SET @startDate = '01/01/2015' SET @endDate = '12/07/2015' SET @currentMonth = DATEPART(mm, @startDate) SET @currentDay = DATEPART(dd, @startDate) SET @currentDate = @startDate WHILE @currentMonth < DATEPART(mm, @endDate) BEGIN SELECT @lastDayOfStartMonth = DATEPART(dd, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@currentDate)+1,0))) PRINT @lastDayOfStartMonth INSERT INTO #VacationDays SELECT DATENAME(month, @currentDate) AS [Month], @lastDayOfStartMonth - @currentDay + 1 AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * @lastDayOfStartMonth - @currentDay + 1 AS totamt SET @currentDate = DATEADD(mm, 1, @currentDate) SET @currentMonth = @currentMonth + 1 SET @currentDay = 1 END IF DATEPART(mm, @startDate) = DATEPART(mm, @endDate) BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @endDate) AS [Month], DATEPART(dd, @endDate) - DATEPART(dd, @startDate) + 1 AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * DATEPART(dd, @endDate) - DATEPART(dd, @startDate) + 1 AS totamt END ELSE BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @endDate) AS [Month], DATEPART(dd, @endDate) AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * DATEPART(dd, @endDate) AS totamt END SELECT * FROM #VacationDays DROP TABLE #VacationDays 

вот результат:

 January 31 31 258.06 8000.00 February 28 28 285.71 8000.00 March 31 31 258.06 8000.00 April 30 30 266.67 8000.00 May 31 31 258.06 8000.00 June 30 30 266.67 8000.00 July 31 31 258.06 8000.00 August 31 31 258.06 8000.00 September 30 30 266.67 8000.00 October 31 31 258.06 8000.00 November 30 30 266.67 8000.00 December 7 30 266.67 1866.67 

проблема заключается в том, что месяцы месяца начинаются неправильно, как в примере dec. имеет 31 день, но получает 30 дней.

Как это решить.

Дата начала и дата окончания могут быть изменены в соответствии с требованиями.

Привет, вы можете использовать этот запрос.

  DECLARE @startDate DATETIME, @endDate DATETIME, @currentDate DATETIME, @currentDay INT, @PerDaycount INT, @Monthcount INT DECLARE @currentMonth INT, @lastDayOfStartMonth INT CREATE TABLE #VacationDays ([Month] VARCHAR(10), [DaysSpent] INT,[MonthDays] VARCHAR(10),[PerdayAmt] decimal(8,2),[TotalAmt] decimal(8,2)) DECLARE @Salary decimal(8,0) SET @Salary = 8000 SET @startDate = '01/01/2015' SET @endDate = '12/07/2015' SET @currentMonth = DATEPART(mm, @startDate) SET @currentDay = DATEPART(dd, @startDate) SET @currentDate = @startDate WHILE @currentMonth <= DATEPART(mm, @endDate) BEGIN SELECT @lastDayOfStartMonth = DATEPART(dd, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@currentDate)+1,0))) PRINT @lastDayOfStartMonth IF(@currentMonth != DATEPART(mm, @endDate)) BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @currentDate) AS [Month], @lastDayOfStartMonth - @currentDay + 1 AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * @lastDayOfStartMonth - @currentDay + 1 AS totamt END ELSE BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @endDate) AS [Month], DATEPART(dd, @endDate) AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * DATEPART(dd, @endDate) AS totamt END SET @currentDate = DATEADD(mm, 1, @currentDate) SET @currentMonth = @currentMonth + 1 SET @currentDay = 1 END SELECT * FROM #VacationDays DROP TABLE #VacationDays 

его немного изменено.

Здесь изменения в соответствии с комментариями ниже

Изменено условие While

 WHILE @currentMonth <= DATEPART(mm, @endDate) 

Потому что @lastDayOfStartMonth вычисляется внутри цикла while. А за декабрьский месяц он не меняется и собирает данные ноябрьского месяца, который равен 30. Поэтому я изменил его, чтобы получить правильные данные внутри самого цикла while.

Повторите вычисление месяца через день после выполнения цикла, потому что ваш цикл while не включает последний месяц, добавьте эту строку после цикла while

  SELECT @lastDayOfStartMonth = DATEPART(dd, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@currentDate)+1,0))) 
 DECLARE @startDate DATETIME, @endDate DATETIME, @currentDate DATETIME, @currentDay INT, @PerDaycount INT, @Monthcount INT DECLARE @currentMonth INT, @lastDayOfStartMonth INT CREATE TABLE #VacationDays ([Month] VARCHAR(10), [DaysSpent] INT,[MonthDays] VARCHAR(10),[PerdayAmt] decimal(8,2),[TotalAmt] decimal(8,2)) DECLARE @Salary decimal(8,0) SET @Salary = 8000 SET @startDate = '01/01/2015' SET @endDate = '12/07/2015' SET @currentMonth = DATEPART(mm, @startDate) SET @currentDay = DATEPART(dd, @startDate) SET @currentDate = @startDate WHILE @currentMonth <= DATEPART(mm, @endDate) BEGIN SELECT @lastDayOfStartMonth = DATEPART(dd, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@currentDate)+1,0))) PRINT @lastDayOfStartMonth INSERT INTO #VacationDays SELECT DATENAME(month, @currentDate) AS [Month], @lastDayOfStartMonth - @currentDay + 1 AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * @lastDayOfStartMonth - @currentDay + 1 AS totamt SET @currentDate = DATEADD(mm, 1, @currentDate) SET @currentMonth = @currentMonth + 1 SET @currentDay = 1 END IF DATEPART(mm, @startDate) = DATEPART(mm, @endDate) BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @endDate) AS [Month], DATEPART(dd, @endDate) - DATEPART(dd, @startDate) + 1 AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * DATEPART(dd, @endDate) - DATEPART(dd, @startDate) + 1 AS totamt END ELSE BEGIN INSERT INTO #VacationDays SELECT DATENAME(month, @endDate) AS [Month], DATEPART(dd, @endDate) AS [DaysSpent],@lastDayOfStartMonth as a,@Salary/@lastDayOfStartMonth As dayammt,(@Salary/@lastDayOfStartMonth ) * DATEPART(dd, @endDate) AS totamt END SELECT * FROM #VacationDays DROP TABLE #VacationDays 

Попробуйте этот код … и проверьте вывод и не выберите последнюю строку из вывода, независимо от того, что возвращается из этого запроса.

Давайте будем гением компьютера.