SQL Server 2019

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

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