My data source looks like below.
And I want output like below.
Basically, it should list each Employee multiple times for each date between those periods.
For quick and easy, below is the Temp Data code.
x
DROP TABLE IF EXISTS #Emp
CREATE TABLE #Emp
(
EmpID VARCHAR(6),
StartDate DATE,
EndDate DATE
)
INSERT INTO #Emp
VALUES ('E001','2021-08-01', '2021-08-05'),
('E002','2021-08-22', '2021-08-22'),
('E003','2021-08-15', '2021-08-17')
Advertisement
Answer
Build a calendar table and join that.
Select e.EmpId, c.Date
from #Emp e
join Calendar c
on c.Date between e.StartDate and e.EndDate