sql - 如何使用 T-SQL 将行标记为长期疾病？

Question

我有一个挑战。我们的企业想知道员工病假是否是长期的。因此，如果疾病是 30 个并发天数的一部分，则所有 30 个或更多记录都应标记为 [long term disease] = True

病假只有周一至周五

我有一张桌子，每个员工在他们生病的每个日历日都有一行，如下所示：

这甚至可以用 T-SQL 解决吗？

标签： sqlsql-servertsqlgaps-and-islands

Answer 1

所以这里有一个简单的解决方案。

我为每个日历日（s 或 w）生成一张病假表

然后，我计算过去 30 个日历日（不包括周末）中没有病假的每一天。

如果那个时期有 0 天，那么它的长期病，所以我们每天都向前看 29 天，看看我们是否在接下来的 30 天内隐藏了长期病。如果是病假，并且在接下来的 29 天内我们将隐藏一段病假，那么病假是长期病假的一部分。

在我的示例中，第二周是病假周，没有标记，四月和可能是病假月，所有天都被标记为长期

WITH dates AS /* Generate som dates */
(
    SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1 n))-1, '2020-01-01') AS DATE) dato
    FROM (VALUES (0), (1), (2), (3), (4)) a (b)
       , (VALUES (0), (1), (2), (3), (4)) b (b)
       , (VALUES (0), (1), (2), (3), (4)) c (b)
),
CTE AS /* Set 2nd week and 3,4 month as sick */
(
    SELECT IIF(MONTH(dato) IN (3, 4) OR DATEPART(WEEK, dato) = 2, 's', 'w') state
         , dato
    FROM dates
),
sums AS /* count days of work last 30 days current day included */
(
    SELECT dato
         , State
         , SUM(IIF(State = 'w' AND DATEPART(WEEKDAY, dato) NOT IN (1, 7), 1, 0)) OVER (ORDER BY dato ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) NonSickDays
    FROM CTE
),
periods AS /* Check if no well days for any day in the next 30 days, current day included */
(
    SELECT dato
         , State
         , MIN(NonSickDays) OVER (ORDER BY dato ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING) Minperiod
         , NonSickDays
    FROM sums
)
SELECT dato /* id sick day, and minimum well days is 0 then its a long term day */
     , State
     , IIF(State = 's' AND Minperiod = 0, 'l', '') longtimesick
     , NonSickDays
FROM periods