SQL 求解每月周末天数
用一段 SQL 查询某月的周末（包含周六，周日）的天数。
用到的方法：
1 多种日期函数
2 Tally Table 校验
原理：
1 求得每月有多少天数
2 计算第一个完整的自然周需要补齐多少天, 并计算本周的实际周末天数
3 将剩下的天数（月总天数- 补掉的天数）除以 7 ， 余数为 0 则取商数 * 2 作为周末天数， 余数不为0， 则去商数 * 2 + 1 作为周末天数
------ 代码实现部分 ---------
为了检验算法的正确性，需要统计出 2014 年到 2018 年的每月周末数量，可以用 Tally Table 计算. Tally Table 的概念，在这篇文章中说得清楚：
/*--------------------------
-
Tally Table 生成 2014 年到 2018 年的日期基础表
-
统计基础表每月的假日数目
-
对比求解每月假日数目的 SQL 脚本
--------------------------/
-- 此处是 1. Tally Table 生成 2014 年到 2018 年的日期基础表 和 2. 统计基础表每月的假日数目
CREATE TABLE FctMonthlyWeekEnds ( CURR_YEAR INT, CURR_MONTH INT, Monthly_WeekEnd_Count INT)
DECLARE @BEGIN DATETIME = DATEADD(D,-1,'2014-01-01')
,@END DATETIME = '2018-12-31'
DECLARE @INC INT;
SELECT @INC = DATEDIFF(DAY, @BEGIN, @END);
WITH L0
AS (SELECT * FROM ( VALUES (1) ,(2) ,(3)) AS T(C)),L1 AS (SELECT a.C,b.C AS BC FROM L0 AS a CROSS JOIN L0 AS b),L2 AS (SELECT a.C
,b.C AS BC FROM L1 AS a CROSS JOIN L1 AS b) ,L3 AS (SELECT a.C,b.C AS BC FROM L2 AS a CROSS JOIN L2 AS b) ,L4 AS (SELECT a.C,b.C AS BC
FROM L3 AS a CROSS JOIN L3 AS b),L5 AS (SELECT a.C,b.C AS BC FROM L4 AS a CROSS JOIN L4 AS b)
INSERT INTO FctMonthlyWeekEnds(CURR_YEAR,CURR_MONTH,Monthly_WeekEnd_Count)
SELECT CURR_YEAR, CURR_MONTH, SUM(dayOfWeek_Flag) AS Monthly_WeekEnd_Count
FROM ( SELECT TOP (@INC) DATEPART(YEAR,DATEADD(DAY, RNK, @BEGIN) ) AS CURR_YEAR
,DATEPART(MONTH,DATEADD(DAY, RNK, @BEGIN) ) AS CURR_MONTH
, DATEADD(DAY, RNK, @BEGIN) AS CURR_DATE,CASE
WHEN DATEPART(DW,DATEADD(DAY, RNK, @BEGIN) ) IN(1,7) THEN 1 ELSE 0
END AS dayOfWeek_Flag FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ( SELECT NULL)) AS RNK FROM L5) M ORDER BY RNK) TMP
GROUP BY CURR_YEAR,CURR_MONTH
ORDER BY CURR_YEAR,CURR_MONTH
--3. 对比求解每月假日数目的 SQL 脚本,同样计算 2014 年到 2018 年的每月周末假期数目CREATE TABLE FctMonthlyWeekEnds_t ( CURR_YEAR INT, CURR_MONTH INT, Monthly_WeekEnd_Count INT)
DECLARE @CURR_DAY DATETIME = CONVERT(DATE,'2014-01-05')
DECLARE @CURR_MONTH_BEGIN_DAY DATETIME = DATEADD(DAY,-1* DAY(@CURR_DAY) + 1, @CURR_DAY)
DECLARE @NEXT_MONTH_BEGIN_DAY DATETIME = DATEADD(MONTH,1,@CURR_MONTH_BEGIN_DAY)
DECLARE @CURR_MONTH_DAY_COUNT INT = DATEDIFF(DAY,@CURR_MONTH_BEGIN_DAY,@NEXT_MONTH_BEGIN_DAY)
DECLARE @CURR_MONTH_REST_DAY INT = 0
WHILE (YEAR(@CURR_DAY)<2019)
BEGIN
SET @CURR_MONTH_REST_DAY = DATEPART(DW,@CURR_MONTH_BEGIN_DAY ) - 8 + @CURR_MONTH_DAY_COUNT
INSERT INTO FctMonthlyWeekEnds_t(CURR_YEAR,CURR_MONTH,Monthly_WeekEnd_Count)
SELECT YEAR(@CURR_DAY) AS C_YEAR,MONTH(@CURR_DAY) AS C_MONTH,HEAD_COUNT + BODY_COUNT
FROM (SELECT CASE WHEN DATEPART(dw, @CURR_MONTH_BEGIN_DAY) = 1 THEN 2 ELSE 1
END AS HEAD_COUNT,
CASE WHEN (@CURR_MONTH_REST_DAY) % 7 = 0 THEN (@CURR_MONTH_REST_DAY / 7 )*2
ELSE FLOOR(@CURR_MONTH_REST_DAY / 7 ) * 2 + 1
END AS BODY_COUNT) T
SELECT @CURR_DAY = DATEADD(MONTH,1,@CURR_DAY)
SELECT @CURR_MONTH_BEGIN_DAY = DATEADD(DAY,-1* DAY(@CURR_DAY) + 1, @CURR_DAY)
SELECT @NEXT_MONTH_BEGIN_DAY = DATEADD(MONTH,1,@CURR_MONTH_BEGIN_DAY)
SELECT @CURR_MONTH_DAY_COUNT = DATEDIFF(DAY,@CURR_MONTH_BEGIN_DAY,@NEXT_MONTH_BEGIN_DAY)
END
-- 对比校验
SELECT T.*, S.*
FROM FctMonthlyWeekEnds_t T
INNER JOIN FctMonthlyWeekEnds S ON T.CURR_YEAR = S.CURR_YEAR AND T.CURR_MONTH = S.CURR_MONTH
WHERE S.Monthly_WeekEnd_Count <> T.Monthly_WeekEnd_Count
