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

Question

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

我们根据周一至周日支付加班费，超过 40 小时。

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

我正在尝试如何编写查询以使我获得额外的结转。

这是我上一个支付期(2019 年 9 月 1 日至 2019 年 9 月 15 日(以及自 1 日星期日以来的每日工作总数的输出。我需要从 2019 年 8 月 26 日开始计算本周的 OT。 在这个特定的员工身上，他在周末随叫随到。 他在 8/16/2019 至 8/31/2019 的工资期内获得了 6.28 小时的加班费，在 2019 年 9 月 1 日额外工作了两个小时，因此 2 小时的 OT 需要结转到 9/1/2019 至 9/15/2019 支票。

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

子查询将给定周的小时数拆分为 2 个不同的支付期(如果适用(。 where 条款将仅包括加班周，并在 2 个支付期之间分配。 选择包括加班的数学。

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是那些日子的时间。根据第 40 小时的工作时间，这个数字可能太高了。在最终输出中，它以该周的总加班时间为上限。

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...)时将星期日作为一周的第一天。