Stored procedure for calculating hours from the 16th of each month to the 15th of each month

I am writing a Stored Procedure (for SQL Server 2012) that needs to calculate the number of hours for our employee from the 16th-15th of every month.

I have the following database structure

My Database Structure

I wrote a stored procedure to calculate the hours, but I think I can only get the start date of the week to filter my state. The stored procedure is giving me the wrong result because the weekly start date is not always the sixteenth.

CREATE PROCEDURE [dbo].[spGetTotalHoursBetween16to15EveryMonth]
AS
BEGIN
    SET NOCOUNT ON 
    BEGIN TRY

    DECLARE @SixteenthDate datetime2(7) = DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
    DECLARE @currentDate datetime2(7) = getDate()
    DECLARE @LastSixteenthDate datetime2(7) = DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)), 0))

    IF(@currentDate >= @SixteenthDate)
    BEGIN
         SELECT 
             (Sum(Day1Hours) + sum(Day2Hours) + Sum(Day3Hours) +
              sum(Day4Hours) + Sum(Day5Hours) + sum(Day6Hours) + Sum(Day7Hours)) AS Total
         FROM 
             dbo.TimeSheets
         WHERE 
             WeekStartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, @SixteenthDate), -1) AND @currentDate 
    END
    ELSE
    BEGIN
        SELECT 
            (Sum(Day1Hours) + sum(Day2Hours) + Sum(Day3Hours) + 
             sum(Day4Hours) + Sum(Day5Hours) + sum(Day6Hours) + Sum(Day7Hours)) AS Total
        FROM 
           dbo.TimeSheets
        WHERE 
           WeekStartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, @LastSixteenthDate), -1) AND @currentDate
    END
    END TRY
    BEGIN CATCH
       THROW
    END CATCH
END

      

+3


source to share


3 answers


I would just do it in a simple way:



declare @today          date = convert(date,current_timestamp)
declare @prev_month_end date = dateadd( day   , -day(@today) , @today          )
declare @period_start   date = dateadd( day   , 16           , @prev_month_end ) -- 16th of THIS month
declare @period_end     date = dateadd( month , 1            , @period_start   ) -- 16th of NEXT month

select @period_start = dateadd(month, -1 , @period_start ) ,
       @period_end   = dateadd(month, -1 , @period_end   )
where day(@today) < 16

select total_hours = coalesce(sum(t.hours),0)
from (           select id = t.id , report_date = dateadd(day,0,t.WeekStartDate) , hours = t.Day1Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,1,t.WeekStartDate) , hours = t.Day2Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,2,t.WeekStartDate) , hours = t.Day3Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,3,t.WeekStartDate) , hours = t.Day4Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,4,t.WeekStartDate) , hours = t.Day5Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,5,t.WeekStartDate) , hours = t.Day6Hours from dbo.TimeSheets t
       union all select id = t.id , report_date = dateadd(day,6,t.WeekStartDate) , hours = t.Day7Hours from dbo.TimeSheets t
     ) t
where t.report_date >= @period_start
  and t.report_date <  @period_end

      

0


source


Always start at the beginning or end of the month.

eg. Here's some logic

Start date = Start date of previous month + 16

End date = start date of current month + 15



Below you can find out the dates

-- First Day of the month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

-- Last Day of previous month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

      

More examples here

0


source


Life is easier with a little data normalization. Don't use a table of tables

CREATE VIEW Timesheets_Normalized AS
SELECT 
  [id]
 ,[Date] = DATEADD(day,[Date_Offset],[WeekStartDate])
 ,[Hours]
FROM MyTable a
UNPIVOT([Hours] FOR [Date_Column] IN (Day1Hours,Day2Hours,Day3Hours,Day4Hours,Day5Hours,Day6Hours,Day7Hours)) b
INNER JOIN (VALUES (0,'Day1Hours'),(1,'Day2Hours'),(2,'Day3Hours'),(3,'Day4Hours'),(4,'Day5Hours'),(5,'Day6Hours'),(6,'Day7Hours')) c([Date_Offset],[Date_Column])
  ON (b.[Date_Column] = c.[Date_Column])

      

Then you can get your answers very simply:

SELECT
  MIN([Date])  AS [PayrollMonthStart]
 ,MAX([Date])  AS [PayrollMonthEnd]
 ,SUM([Hours]) AS [TotalHours]
FROM Timesheets_Normalized
GROUP BY YEAR(DATEADD(day,-15,[Date])),MONTH(DATEADD(day,-15,[Date]))
HAVING CAST(GETDATE() AS date) BETWEEN MIN([Date]) AND MAX([Date])

      

0


source







All Articles