sql - 计算每月 16 日到 15 日的小时数的存储过程

Question

我正在编写一个存储过程(适用于 SQL Server 2012)，该过程应该计算我们员工每月 16 日至 15 日的工作小时数。

我有以下数据库结构

我已经编写了一个存储过程来计算小时数，但我认为我只能获取周开始日期来过滤我的条件。存储过程返回错误结果，因为每周开始日期并不总是 16 号。

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

Answer 1

我可能只是用简单的方法来做:

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