sql - 如何在给定的日期列中查找每月的星期一作为sql中的行

Question

这些所有日期都是 10 月的星期一，我希望它动态地进行。如果可以仅对 Mon, Tue, Wed 做同样的事情会更好，最后一列应该是 sum[正如在 excel 表中显示的那样

选择 ClientName,JobName, JobTime, [2018-09-03], [2018-09-10], [2018-09-17], [2018-09-24] from (select ClientName,JobName, JobTime, convert(date ,reporttime,101) as [ReportDate] from StaleFileReport where convert(varchar,ReportTime,102) in ('2018.09.03','2018.09.10','2018.09.17','2018.09.24') group by ClientName, JobName,JobTime, convert(date,reporttime,101))x PIVOT ( Count([ReportDate]) FOR [ReportDate] in ( [2018-09-03], [2018-09-10], [2018-09-17 ], [2018-09-24]) )

标签： sqlsql-servertsqlsql-server-2008ssms

Answer 1

您可以使用如下脚本：

DECLARE @PassedInDate AS DATE;

SET @PassedInDate = GETDATE();

WITH    CTE ( N )
          AS (
               SELECT   1
               FROM     ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) a ( N )
               ),
        CTE2 ( N )
          AS (
               SELECT   0 -- 0 based
               UNION ALL
               SELECT TOP 30 -- limit it up front, never more than 31 days in a month!
                        ROW_NUMBER() OVER ( ORDER BY (SELECT   NULL) )
               FROM     CTE a
               CROSS JOIN CTE b
             ),
        CTE3 ( N )
          AS (
               SELECT   DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0))
               FROM     CTE2
               WHERE    DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0)) < 
                        DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate) + 1, 0)
             )
SELECT  N AS [DATE]
FROM    CTE3
WHERE DATENAME(WEEKDAY, N) = 'Monday';

参考：https ://www.sqlservercentral.com/Forums/Topic1654075-391-1.aspx