SQLServer-檢索兩列之間的月份日期串列

我想添加一個新列，該列應包含兩個單獨列中的 startdate 和 enddate 之間的月份。

我當前的資料看起來像這樣：

我想要的輸出是：

uj5u.com熱心網友回復：

（編輯：Aaron 的評論是一個很好的觀點 - 我已經對他談到的資料做出了確切的假設 - 即如果你的日期總是每個月的 15 號，這一切都很好。如果這不是真的，你可能需要做一些作業做，所以要么改進你的問題，要么從這個起點自己解決）

這應該讓你得到你想要的

CREATE TABLE #data
(
    [Case] INT,
    Name    VARCHAR(3),
    StartDate DATE,
    EndDate DATE
);

INSERT INTO #data
VALUES
(1,'ABC','2021-01-15','2021-03-15'),
(2,'DEF','2021-03-15','2021-05-15');

WITH cte([Case], [Name], dt) AS 
(
    SELECT [Case], [Name], StartDate AS dt
    FROM #data 
    UNION ALL
    SELECT cte.[Case], cte.Name, DATEADD(MONTH, 1, dt)
    FROM cte 
    INNER JOIN #data ON #data.[Case] = cte.[Case] AND #data.[Name] = cte.[Name]
    WHERE dt < EndDate
)
SELECT *
FROM cte
ORDER BY cte.[Case], cte.Name, cte.dt

但實際上，像這樣的遞回查詢很難維護。我會創建一個“月份”表，其中包含幾個月的負載，然后您可以加入到開始日期和結束日期之間的月份：

CREATE TABLE #months -- This would be a concrete table in your db, not temp
(
    MonthDate DATE
)

INSERT INTO #months -- you would need to populate this with enough months to satisfy your needs
VALUES 
('2021-01-15'),
('2021-02-15'),
('2021-03-15'),
('2021-04-15'),
('2021-05-15'),
('2021-06-15');

SELECT d.[Case], d.[Name], m.MonthDate
FROM #data d
INNER JOIN #months m ON m.MonthDate BETWEEN d.StartDate AND d.EndDate
ORDER BY d.[Case], d.[Name], m.MonthDate

uj5u.com熱心網友回復：

請參閱（在日期范圍之間生成日期）
使用交叉聯接將 tmp 表與日期生成器代碼結合起來

declare @tmp as table ( [Case] int, [Name] varchar(20), [StartDate] date, [EndDate] date)

insert into @tmp
values(1,   'ABC',  '2021-01-15',   '2021-03-15')
,(2,    'DEF'   ,'2021-03-15',  '2021-05-15')


SELECT  
tmp.[Case],
tmp.[Name],
DATEADD(DAY, Nbr - 1, tmp.StartDate) MonthList
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
        cross join
        (
            select [Name],[Case],[StartDate],[EndDate] from @tmp
        )tmp
WHERE   Nbr - 1 <= DATEDIFF(DAY, tmp.StartDate, tmp.EndDate)
order by
tmp.[Case],
tmp.[Name],
Detail_Date

輸出：

    Case    Name    MonthList
1   ABC 2021-01-15
1   ABC 2021-01-16
1   ABC 2021-01-17
1   ABC 2021-01-18
1   ABC 2021-01-19
1   ABC 2021-01-20
1   ABC 2021-01-21
1   ABC 2021-01-22
1   ABC 2021-01-23
1   ABC 2021-01-24
1   ABC 2021-01-25
1   ABC 2021-01-26
1   ABC 2021-01-27
1   ABC 2021-01-28
1   ABC 2021-01-29
1   ABC 2021-01-30
1   ABC 2021-01-31
1   ABC 2021-02-01
1   ABC 2021-02-02
1   ABC 2021-02-03
1   ABC 2021-02-04
1   ABC 2021-02-05
1   ABC 2021-02-06
1   ABC 2021-02-07
1   ABC 2021-02-08
1   ABC 2021-02-09
1   ABC 2021-02-10
1   ABC 2021-02-11
1   ABC 2021-02-12
1   ABC 2021-02-13
1   ABC 2021-02-14
1   ABC 2021-02-15
1   ABC 2021-02-16
1   ABC 2021-02-17
1   ABC 2021-02-18
1   ABC 2021-02-19
1   ABC 2021-02-20
1   ABC 2021-02-21
1   ABC 2021-02-22
1   ABC 2021-02-23
1   ABC 2021-02-24
1   ABC 2021-02-25
1   ABC 2021-02-26
1   ABC 2021-02-27
1   ABC 2021-02-28
1   ABC 2021-03-01
1   ABC 2021-03-02
1   ABC 2021-03-03
1   ABC 2021-03-04
1   ABC 2021-03-05
1   ABC 2021-03-06
1   ABC 2021-03-07
1   ABC 2021-03-08
1   ABC 2021-03-09
1   ABC 2021-03-10
1   ABC 2021-03-11
1   ABC 2021-03-12
1   ABC 2021-03-13
1   ABC 2021-03-14
1   ABC 2021-03-15
2   DEF 2021-03-15
2   DEF 2021-03-16
2   DEF 2021-03-17
2   DEF 2021-03-18
2   DEF 2021-03-19
2   DEF 2021-03-20
2   DEF 2021-03-21
2   DEF 2021-03-22
2   DEF 2021-03-23
2   DEF 2021-03-24
2   DEF 2021-03-25
2   DEF 2021-03-26
2   DEF 2021-03-27
2   DEF 2021-03-28
2   DEF 2021-03-29
2   DEF 2021-03-30
2   DEF 2021-03-31
2   DEF 2021-04-01
2   DEF 2021-04-02
2   DEF 2021-04-03
2   DEF 2021-04-04
2   DEF 2021-04-05
2   DEF 2021-04-06
2   DEF 2021-04-07
2   DEF 2021-04-08
2   DEF 2021-04-09
2   DEF 2021-04-10
2   DEF 2021-04-11
2   DEF 2021-04-12
2   DEF 2021-04-13
2   DEF 2021-04-14
2   DEF 2021-04-15
2   DEF 2021-04-16
2   DEF 2021-04-17
2   DEF 2021-04-18
2   DEF 2021-04-19
2   DEF 2021-04-20
2   DEF 2021-04-21
2   DEF 2021-04-22
2   DEF 2021-04-23
2   DEF 2021-04-24
2   DEF 2021-04-25
2   DEF 2021-04-26
2   DEF 2021-04-27
2   DEF 2021-04-28
2   DEF 2021-04-29
2   DEF 2021-04-30
2   DEF 2021-05-01
2   DEF 2021-05-02
2   DEF 2021-05-03
2   DEF 2021-05-04
2   DEF 2021-05-05
2   DEF 2021-05-06
2   DEF 2021-05-07
2   DEF 2021-05-08
2   DEF 2021-05-09
2   DEF 2021-05-10
2   DEF 2021-05-11
2   DEF 2021-05-12
2   DEF 2021-05-13
2   DEF 2021-05-14
2   DEF 2021-05-15

標籤：sql sql-server 日期

上一篇：SQL查詢-回圈

下一篇：如何用兩個外鍵創建一個復合鍵