sql - 月份日历查询

Question

我的查询是这样的：

当我整个月都过得很好但例如。1-2018, 1-2019 得到错误的结果

IF OBJECT_ID('tempdb..#CAL_DATE','TABLE') is not null DROP TABLE #CAL_DATE;
if OBJECT_ID('tempdb..#MONTH_CAL','TABLE') is not null DROP TABLE #MONTH_CAL;
DECLARE @month INT , @year INT;

SET @month = 1;
SET @year = 2018;

DECLARE @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)
DECLARE @START_DATE DATETIME = DATEADD(DAY, -((DATEPART(WEEKDAY, @fromDate)-1)), @fromDate);
DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  

IF (DATEPART(WEEKDAY, @fromDate) = 1)
    SET @START_DATE = DATEADD(DAY, -7, @START_DATE)

;WITH mycte AS 
(
        SELECT @START_DATE AS dt, CAST(1 AS INT) SR
         UNION ALL
         SELECT dt + 1, SR + 1
         FROM   mycte
         WHERE  mycte.SR < 42
)
SELECT cast(dt as date) TRANS_DATE 
INTO #CAL_DATE
FROM mycte
SELECT MIN(SUN) SUN, MIN([MON]) MON, MIN([TUE]) TUE, MIN([WED]) WED, MIN(THU) THU,MIN(FRI) FRI, MIN(SAT) SAT
INTO #MONTH_CAL
FROM
(
    SELECT TRANS_DATE, 
    DATEPART(WEEK, TRANS_DATE) WK_OF_YR,
    DAY(TRANS_DATE) DY, 
    UPPER(LEFT(DATENAME(dw,TRANS_DATE),3)) DY_NM, 
    DATEPART(WEEKDAY, TRANS_DATE) WK_DY
    FROM #CAL_DATE 
)MAIN
PIVOT
(
    MAX(TRANS_DATE) FOR [DY_NM] IN ([SUN],[MON],[TUE],[WED],[THU],[FRI],[SAT])
)P
GROUP BY P.WK_OF_YR
ORDER BY WK_OF_YR

SELECT * FROM #MONTH_CAL

在我没有得到正确的结果之后，我已经通过了月份和年份，我想得到这样的结果。

如果我通过了 1-2018（例如月年）需要这样的结果。

标签： sqlsql-serversql-server-2008

Answer 1

您需要另一行来将工作日排名/分组到他们自己的类别中，并防止返回单行。我用RANK. 此外，我使用了一个计数表而不是递归 CTE 来使这变得更容易和更快。

declare @startDate date = '20160530'
declare @endDate date = '20170103'
declare @days int = datediff(day,@startDate, @endDate)

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )


select 
    p.SUN
    ,p.MON
    ,p.TUE
    ,p.WED
    ,p.THU
    ,p.FRI
    ,p.SAT
    --,p.RNK     --uncomment this out to see the grouping
from 
    (select 
        TheDate = dateadd(day,N,@startDate)
        ,DOW = 
            case 
                when datepart(weekday,dateadd(day,N,@startDate)) = 1 then 'SUN'
                when datepart(weekday,dateadd(day,N,@startDate)) = 2 then 'MON'
                when datepart(weekday,dateadd(day,N,@startDate)) = 3 then 'TUE'
                when datepart(weekday,dateadd(day,N,@startDate)) = 4 then 'WED'
                when datepart(weekday,dateadd(day,N,@startDate)) = 5 then 'THU'
                when datepart(weekday,dateadd(day,N,@startDate)) = 6 then 'FRI'
                when datepart(weekday,dateadd(day,N,@startDate)) = 7 then 'SAT'
            end
        ,RNK = rank() over(partition by datepart(weekday,dateadd(day,N,@startDate)) order by dateadd(day,N,@startDate))
    from cteTally
    where N <= @days) x
pivot
(
MAX(TheDate) FOR [DOW] IN ([SUN],[MON],[TUE],[WED],[THU],[FRI],[SAT])
) p