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
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
source to share
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
source to share
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
source to share
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])
source to share