sql - 如何让日期时间不插入到表中

Question

我有一些这样的数据。

with WorkingTime as(
    select 1 Userid, '2019-04-03' RegDay, '08:48' CheckIn, '18:00' CheckOut union all
    select 1 , '2019-04-05' , '08:00' , '18:00'  union all
    select 1 , '2019-04-06' , '07:48' , '18:00'  union all
    select 1 , '2019-04-09' , '08:20' , '18:00'  
)

现在我想统计 UserId 1 的 DateOff。

select Userid
     ,sum(case when CheckIn < '08:00' then 1 else 0 end) as TotalLate
     ,'?' as TotalDateOff 
from WorkingTime 
where RegDay between '2019-04-03' and '2019-04-11'
group by Userid

从'2019-04-03'到'2019-04-11'的日期是'2019-04-04','2019-04-07','2019-04-10','2019-04 -11'。这意味着列 TotalDateOff 是 4。我怎样才能做到这一点？

Answer 1

一种方法是使用 CTE 获取日期列表(从 - 到)，该列表将按天或按月或任何所需的增量方法递增。

您可以在当前 CTE 中使用它，方法是将它添加到当前 CTE，这将获得最小日期并增加 1 天，直到达到最大日期。然后，您只需要一个简单的左连接，它将连接日期，如果 WorkingTime 中不存在该日期，它将为 NULL 值。然后，您需要做的就是计算这些空值以获得总的 dateoff。

示例:

;with WorkingTime as(
    select 1 Userid, '2019-04-03' RegDay, '08:48' CheckIn, '18:00' CheckOut union all
    select 1 , '2019-04-05' , '08:00' , '18:00'  union all
    select 1 , '2019-04-06' , '07:48' , '18:00'  union all
    select 1 , '2019-04-09' , '08:20' , '18:00'  
) , WorkingOff AS (
    SELECT Userid, CAST(MIN(RegDay) AS DATE) MinRegDay
    FROM WorkingTime
    GROUP BY Userid
    UNION ALL 
    SELECT Userid,  DATEADD(DAY, 1, MinRegDay)
    FROM WorkingOff
    WHERE 
        DATEADD(DAY, 1, MinRegDay) < '2019-04-11'
) 
SELECT 
    wf.Userid
,   SUM(CASE WHEN wt.CheckIn < '08:00' THEN 1 ELSE 0 END) TotalLate
,   SUM(CASE WHEN wt.RegDay IS NULL THEN 1 ELSE 0 END) TotalDateOff 
FROM WorkingOff wf
LEFT JOIN WorkingTime wt ON wf.MinRegDay = wt.RegDay AND wf.Userid = wt.Userid
where wf.MinRegDay between '2019-04-03' and '2019-04-11'
GROUP BY wf.Userid