By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Item (
Item_No int PRIMARY KEY IDENTITY ,
ActivityName NVARCHAR(200),
StartDate DATETIME,
EndDate DATETIME,
)
INSERT INTO Item
(ActivityName, StartDate, EndDate)
VALUES
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-07-29', '2020-08-03'),
('test04', '2020-08-29', '2020-09-03'),
('test05', '2020-09-10', '2020-09-13'),
('test06', '2020-08-14', '2020-08-16')
6 rows affected
WITH dates AS (
SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
FROM Item
WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
(EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
UNION ALL
SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
FROM dates
WHERE Date < EndDate
)
SELECT ActivityName,
CAST([Date] AS DATE) AS [Date],
CAST(StartDate AS DATE) AS StartDate,
CAST(EndDate AS DATE) AS EndDate
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY StartDate, [Date];
| ActivityName | Date | StartDate | EndDate |
|---|---|---|---|
| test03 | 2020-08-01 | 2020-07-29 | 2020-08-03 |
| test03 | 2020-08-02 | 2020-07-29 | 2020-08-03 |
| test03 | 2020-08-03 | 2020-07-29 | 2020-08-03 |
| test01 | 2020-08-05 | 2020-08-05 | 2020-08-09 |
| test01 | 2020-08-06 | 2020-08-05 | 2020-08-09 |
| test01 | 2020-08-07 | 2020-08-05 | 2020-08-09 |
| test01 | 2020-08-08 | 2020-08-05 | 2020-08-09 |
| test01 | 2020-08-09 | 2020-08-05 | 2020-08-09 |
| test02 | 2020-08-07 | 2020-08-07 | 2020-08-11 |
| test02 | 2020-08-08 | 2020-08-07 | 2020-08-11 |
| test02 | 2020-08-09 | 2020-08-07 | 2020-08-11 |
| test02 | 2020-08-10 | 2020-08-07 | 2020-08-11 |
| test02 | 2020-08-11 | 2020-08-07 | 2020-08-11 |
| test06 | 2020-08-14 | 2020-08-14 | 2020-08-16 |
| test06 | 2020-08-15 | 2020-08-14 | 2020-08-16 |
| test06 | 2020-08-16 | 2020-08-14 | 2020-08-16 |
| test04 | 2020-08-29 | 2020-08-29 | 2020-09-03 |
| test04 | 2020-08-30 | 2020-08-29 | 2020-09-03 |
| test04 | 2020-08-31 | 2020-08-29 | 2020-09-03 |
WITH dates AS (
SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
FROM Item
WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
(EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
UNION ALL
SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
FROM dates
WHERE Date < EndDate
)
SELECT DISTINCT CAST([Date] AS DATE) AS [Date]
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY [Date]
| Date |
|---|
| 2020-08-01 |
| 2020-08-02 |
| 2020-08-03 |
| 2020-08-05 |
| 2020-08-06 |
| 2020-08-07 |
| 2020-08-08 |
| 2020-08-09 |
| 2020-08-10 |
| 2020-08-11 |
| 2020-08-14 |
| 2020-08-15 |
| 2020-08-16 |
| 2020-08-29 |
| 2020-08-30 |
| 2020-08-31 |