sql - 如何计算SQL查询中除“周末”以外的工作日？

我想计算一下我的哪些假期和工作日有冲突。
我有一张假日餐桌，在下面。

NameOfHoliday StartDate DurationByDay
Christmas     26.12.2015 5

26号和27号在周末。所以这不应该计算。所以我只需要4天。

最佳答案

DECLARE @t TABLE(
   NameOfHoliday VARCHAR(10),
   StartDate DATE,
   DurationByDay SMALLINT
)

INSERT INTO @t
VALUES ('Christmas', '20151226', 5)

;WITH cte AS
(
    SELECT *, cnt = 0
    FROM @t

    UNION ALL

    SELECT t2.NameOfHoliday, DATEADD(DAY, t1.cnt + 1, t2.StartDate), t2.DurationByDay, t1.cnt + 1
    FROM cte t1
    JOIN @t t2 ON t1.NameOfHoliday = t2.NameOfHoliday
    WHERE t1.cnt < t1.DurationByDay
)
SELECT NameOfHoliday, StartDate, DT
FROM (
    SELECT *
        , DT = DATENAME(DW, StartDate)
        , RowNum = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY NameOfHoliday)
    FROM cte
) t
WHERE RowNum = 1
    AND DT NOT IN ('Saturday', 'Sunday')
OPTION (MAXRECURSION 0)

输出-

NameOfHoliday StartDate  DT
------------- ---------- ------------------
Christmas     2015-12-28 Monday
Christmas     2015-12-29 Tuesday
Christmas     2015-12-30 Wednesday
Christmas     2015-12-31 Thursday

关于sql - 如何计算SQL查询中除“周末”以外的工作日？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34634701/