如何在 SQL 中将小时划分为下一个工作日? [英] How can I divide hours to next working days in SQL?

问题描述

我有一个表，用于存储开始日期和小时数.我还有另一个时间表作为工作日的参考.我的主要目标是将这些时间划分为工作日.

I have a table that stores the start-date and number of the hours. I have also another time table as reference to working days. My main goal is the divide this hours to the working days.

例如:

ID   Date         Hour
1     20210504     40

我希望它的结构为

ID      Date         Hour
1       20210504     8
1       20210505     8
1       20210506     8
1       20210507     8
1       20210510     8

我设法用给定的代码划分了时间，但无法在工作日内完成.

I manage to divide the hours with the given code but couldn't manage to make it in working days.

WITH cte1 AS 
(
  select 1 AS ID, 20210504 AS Date, 40 AS Hours --just a test case
), working_days AS 
(
select date from dateTable
),
cte2 AS 
(
select ID, Date, Hours, IIF(Hours<=8, Hours, 8) AS dailyHours FROM cte1

UNION ALL

SELECT 
cte2.ID,
cte2.Date + 1
,cte2.Hours - 8
,IIF(Hours<=8, Hours, 8)
FROM cte2
JOIN cte1 t ON cte2.ID = t.ID
WHERE cte2.HOURS > 8 AND cte2.Date + 1 IN (select * from working_days)

当我像这样使用它时，它只会给我这个输出，但缺少一天

When I use it like this it only gives me this output with one day missing

ID      Date         Hour
1       20210504     8
1       20210505     8
1       20210506     8
1       20210507     8

推荐答案

要解决您的问题，您需要以正确的方式构建日历，还要向 working_days 添加一个 ROW_NUMBER 以获得正确的进度.

To solve your problem you need to build your calendar in the right way, adding also to working_days a ROW_NUMBER to get correct progression.

declare @date_start date = '2021-05-01'

;WITH
cte1 AS (
    SELECT * FROM 
    (VALUES
    (1, '20210504', 40),
    (2, '20210505', 55),
    (3, '20210503', 44)
    ) X (ID, Date, Hour)
),
numbers as (
    SELECT ROW_NUMBER() over (order by o.object_id) N
    FROM sys.objects o
),
cal as (
    SELECT cast(DATEADD(day, n, @date_start) as date) d, n-1 n
    FROM numbers n
    where n.n<32
),
working_days  as (
    select d, ROW_NUMBER() over (order by n) dn
    from cal
    where DATEPART(weekday, d) < 6 /* monday to friday in italy (country dependent) */
),
base as (
    SELECT t.ID, t.Hour, w.d, w.dn
    from cte1 t
    join working_days w on w.d = t.date
)
SELECT t.ID, w.d, iif((8*n)<=Hour, 8, 8 + Hour - (8*n) ) h
FROM base t
join numbers m on m.n <= (t.Hour / 8.0) + 0.5
join working_days w on w.dn = t.dn + N -1
order by 1,2

这篇关于如何在 SQL 中将小时划分为下一个工作日?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！