根据两个日期之间的周/年计算加班

Question

我们每个月付两次。支付期为1-15和16-EOM。因此，支付期的结束可以在任何一天结束。

我们的加班费是以周一至周日超过40小时为基础的。

如果支付期在周六结束，员工有47小时，我们将支付7小时的支票。如果员工在星期天工作，而现在一周的总工作时间是52小时。我们会在他们的下一张支票上付5个小时..目前，这是一个手动计算过程。

我正在努力思考如何编写一个查询来获得额外的进位。

这是我上一个支付期从2019年9月1日到2019年9月15日的每日工作总和，自1日以来是周日。我需要计算从2019年8月26日开始的一周的加班时间。在这个特殊的员工身上，他周末随叫随到。他在2019年8月16日至2019年8月31日期间加班6.28小时，2019年9月1日额外工作2小时，因此2小时加班需要结转到2019年9月1日至2019年9月15日。

ID HRS WK CDATE STU02 8.16 35 2019-08-26 00:00:00.000 STU02 9.37 35 2019-08-27 00:00:00.000 STU02 9.07 35 2019-08-28 00:00:00.000 STU02 7.91 35 2019-08-29 00:00:00.000 STU02 9.12 35 2019-08-30 00:00:00.000 STU02 2.65 35 2019-08-31 00:00:00.000 STU02 2.00 35 2019-09-01 00:00:00.000 STU02 4.17 36 2019-09-02 00:00:00.000 STU02 9.40 36 2019-09-03 00:00:00.000 STU02 8.80 36 2019-09-04 00:00:00.000 STU02 8.90 36 2019-09-05 00:00:00.000 STU02 8.93 36 2019-09-06 00:00:00.000 STU02 2.56 36 2019-09-07 00:00:00.000 STU02 2.00 36 2019-09-08 00:00:00.000 STU02 8.66 37 2019-09-09 00:00:00.000 STU02 9.14 37 2019-09-10 00:00:00.000 STU02 9.07 37 2019-09-11 00:00:00.000 STU02 9.29 37 2019-09-12 00:00:00.000 STU02 9.94 37 2019-09-13 00:00:00.000 STU02 2.00 37 2019-09-15 00:00:00.000

我很感谢大家的帮助，我尝试过的许多不同的东西都让我抓狂。

**通过表和数据进行更新**

/****** Object:  Table [dbo].[DLI_TEST_DATE]    Script Date: 9/18/2019 3:50:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DLI_TEST_DATE](
    [EMPLOYEE_ID] [nvarchar](15) NULL,
    [REG_TOTAL] [float] NULL,
    [WEEK_NUM] [int] NULL,
    [CDATE] [datetime] NULL,
    [DAYOFWK] [int] NULL
) ON [PRIMARY]

GO

INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 2, 35, CAST(N'2019-08-25 00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 8.16, 35, CAST(N'2019-08-26 00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.37, 35, CAST(N'2019-08-27 00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.07, 35, CAST(N'2019-08-28 00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 7.91, 35, CAST(N'2019-08-29 00:00:00.000' AS DateTime), 5)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.12, 35, CAST(N'2019-08-30 00:00:00.000' AS DateTime), 6)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 2.65, 35, CAST(N'2019-08-31 00:00:00.000' AS DateTime), 7)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 2, 36, CAST(N'2019-09-01 00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 4.17, 36, CAST(N'2019-09-02 00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.4, 36, CAST(N'2019-09-03 00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 8.8, 36, CAST(N'2019-09-04 00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 8.9, 36, CAST(N'2019-09-05 00:00:00.000' AS DateTime), 5)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 8.93, 36, CAST(N'2019-09-06 00:00:00.000' AS DateTime), 6)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 2.56, 36, CAST(N'2019-09-07 00:00:00.000' AS DateTime), 7)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 2, 37, CAST(N'2019-09-08 00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 8.66, 37, CAST(N'2019-09-09 00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.14, 37, CAST(N'2019-09-10 00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.07, 37, CAST(N'2019-09-11 00:00:00.000' AS DateTime), 4)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.29, 37, CAST(N'2019-09-12 00:00:00.000' AS DateTime), 5)
GO
INSERT [dbo].[DLI_TEST_DATE] ([EMPLOYEE_ID], [REG_TOTAL], [WEEK_NUM], [CDATE], [DAYOFWK]) VALUES (N'STU02', 9.94, 37, CAST(N'2019-09-13 00:00:00.000' AS DateTime), 6)
GO

Answer 1

如果适用，该子查询将给定一周的小时划分为两个不同的支付期间。where子句将只包括加班的周，并且在两个支付期之间拆分。Select包括加班的数学计算。

declare @DLI_TEST_DATA TABLE (
    [EMPLOYEE_ID] [nvarchar](15) NULL,
    [REG_TOTAL] [float] NULL,
    [WEEK_NUM] [int] NULL,
    [CDATE] [datetime] NULL,
    [DAYOFWK] [int] NULL
) 

INSERT into @DLI_TEST_DATA
    VALUES (N'STU02', 2, 34, CAST(N'2019-08-25 00:00:00.000' AS DateTime), 1)
        ,(N'STU02', 8.16, 35, CAST(N'2019-08-26 00:00:00.000' AS DateTime), 2)
        ,(N'STU02', 9.37, 35, CAST(N'2019-08-27 00:00:00.000' AS DateTime), 3)
        ,(N'STU02', 9.07, 35, CAST(N'2019-08-28 00:00:00.000' AS DateTime), 4)
        ,(N'STU02', 7.91, 35, CAST(N'2019-08-29 00:00:00.000' AS DateTime), 5)
        ,(N'STU02', 9.12, 35, CAST(N'2019-08-30 00:00:00.000' AS DateTime), 6)
        ,(N'STU02', 2.65, 35, CAST(N'2019-08-31 00:00:00.000' AS DateTime), 7)
        ,(N'STU02', 2, 35, CAST(N'2019-09-01 00:00:00.000' AS DateTime), 1)
        ,(N'STU02', 4.17, 36, CAST(N'2019-09-02 00:00:00.000' AS DateTime), 2)
        ,(N'STU02', 9.4, 36, CAST(N'2019-09-03 00:00:00.000' AS DateTime), 3)
        ,(N'STU02', 8.8, 36, CAST(N'2019-09-04 00:00:00.000' AS DateTime), 4)
        ,(N'STU02', 8.9, 36, CAST(N'2019-09-05 00:00:00.000' AS DateTime), 5)
        ,(N'STU02', 8.93, 36, CAST(N'2019-09-06 00:00:00.000' AS DateTime), 6)
        ,(N'STU02', 2.56, 36, CAST(N'2019-09-07 00:00:00.000' AS DateTime), 7)
        ,(N'STU02', 2, 36, CAST(N'2019-09-08 00:00:00.000' AS DateTime), 1)
        ,(N'STU02', 8.66, 37, CAST(N'2019-09-09 00:00:00.000' AS DateTime), 2)
        ,(N'STU02', 9.14, 37, CAST(N'2019-09-10 00:00:00.000' AS DateTime), 3)
        ,(N'STU02', 9.07, 37, CAST(N'2019-09-11 00:00:00.000' AS DateTime), 4)
        ,(N'STU02', 9.29, 37, CAST(N'2019-09-12 00:00:00.000' AS DateTime), 5)
        ,(N'STU02', 9.94, 37, CAST(N'2019-09-13 00:00:00.000' AS DateTime), 6)

select WEEK_NUM,fullweek - (case when endfirstperiod+endsecondperiod <= 40 then 40.0 else endfirstperiod+endsecondperiod end) OvertimeCarriedOver
from (
    select week_num,sum(reg_total) fullweek
        ,sum(case when day(cdate) between 10 and 15 then reg_total else 0 end) endfirstperiod
        ,sum(case when day(cdate) between 16 and 21 then reg_total else 0 end) beginsecondperiod
        ,sum(case when day(cdate) between day(eomonth(cdate)) - 5 and day(eomonth(cdate)) then reg_total else 0 end) endsecondperiod
        ,sum(case when day(cdate) between 1 and 6 then reg_total else 0 end) beginfirstperiod
    from @DLI_TEST_DATA
    group by week_num
) basic
where fullweek > 40.0
    and beginfirstperiod+beginsecondperiod > 0
    and endfirstperiod+endsecondperiod > 0
order by week_num

Answer 2

其核心思想是将所有内容分组为逻辑周，然后查看与其关联的支付期与该周的第一天不匹配的日期。

carryover_max是那些日子的小时数。根据工作时间的不同，这个数字可能太高了。在最终输出中，它以该周的总加班为上限。

with weeks as (
    select week_start, week_end,
        case when sum(REG_TOTAL) > 40
             then sum(REG_TOTAL) - 40 else 0 end as overtime_total,
        case when sum(REG_TOTAL) > 40
             then sum(case when period <> week_period then REG_TOTAL else 0 end)
             else 0 end carryover_max
    from
        dbo.DLI_TEST_DATE cross apply (
            select
                dateadd(day, -datepart(weekday, dateadd(day, -1, cdate)) + 1, cdate)
        ) as v(week_start) cross apply (
            select
                dateadd(day, 6, week_start),
                case when datepart(day, week_start) <= 15 then 1 else 2 end,
                case when datepart(day, cdate) <= 15 then 1 else 2 end
        ) as v2(week_end, week_period, period)
    group by week_start, week_end
), periods as (
    select distinct period_start, period_end
    from
        dbo.DLI_TEST_DATE cross apply (
           select
                datefromparts(datepart(year, cdate), datepart(month, cdate),
                    case when datepart(day, cdate) <= 15 then 1 else 15 end),
                datefromparts(datepart(year, cdate), datepart(month, cdate),
                    case when datepart(day, cdate) <= 15 then 16 else datepart(day, eomonth(cdate)) end)
        ) v(period_start, period_end)
)
select period_start,
    sum(case when carryover_max > overtime_total then overtime_total else carryover_max end) as overtime_owed
from
    periods inner join
        weeks w on w.week_end >= period_start and w.week_end <= period_end
group by period_start;

https://rextester.com/TVXF30798

顺便说一句，您真的不想为您的数据类型使用float。

因为有一个关于星期编号的问题，所以我刚刚计算了日期。无论如何，这应该会在新年的几周内更好地发挥作用。此外，我还假设在使用datepart(weekday...)时，您的服务器设置将星期天作为一周的第一天。