clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228811 fiddles created (16674 in the last week).

select @@version;
(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
 hidden batch(es)


CREATE TABLE Item ( Item_No int PRIMARY KEY IDENTITY , ActivityName NVARCHAR(200), StartDate DATETIME, EndDate DATETIME, )
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)