在SQL Server中，如何获取给定月份范围内按月分组的周末总天数？

debugcn 发表于 Dev

Arjun

如何在SQL Server中获取给定月份范围内按月分组的周末（星期日）总计数？

例如：所选日期：“ 2020年10月”至“ 2020年12月”

月份名称	计数
十月	4
十一月	4
十二月	5

巴巴罗斯·奥赞

您可以使用DATEDIFF()和DATENAME()函数以及递归CTE，例如

DECLARE @date1 AS DATE, @date2 AS DATE;
SET @date1 = '2020-10-01';
SET @date2 = '2020-12-31';

WITH cte AS 
(
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 AS value
    FROM cte
   WHERE cte.n < DATEDIFF(day,@date1,@date2) 
)
SELECT DATENAME(MONTH,DATEADD(day, n, @date1 ) ) AS [Month Name], COUNT(*) AS [Count]
  FROM cte
 WHERE DATENAME(WEEKDAY, DATEADD(day, n, @date1 ))= 'Sunday'
 GROUP BY DATENAME(MONTH,DATEADD(day, n, @date1 ))

Demo

如果日期间隔超过一年，则使用以下代码，其中还包括订购。

DECLARE @date1 AS DATE, @date2 AS DATE;
SET @date1 = '2020-10-01';
SET @date2 = '2021-03-31';

WITH cte AS 
(
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 AS value
    FROM cte
   WHERE cte.n < DATEDIFF(day,@date1,@date2) 
)
SELECT DATEPART(yy, DATEADD(day, n, @date1)) AS [Year],
       DATENAME(MONTH,DATEADD(day, n, @date1)) AS [Month Name], 
       COUNT(*) AS [Count]
  FROM cte
 WHERE DATENAME(WEEKDAY, DATEADD(day, n, @date1))= 'Sunday'
 GROUP BY DATENAME(MONTH,DATEADD(day, n, @date1)),
          DATEPART(yy, DATEADD(day, n, @date1)),
          DATEPART(m, DATEADD(day, n, @date1))
 ORDER BY [Year], DATEPART(m, DATEADD(day, n, @date1))
 OPTION (MAXRECURSION 0)

Demo

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。