Adi Cohn-120898 (9/3/2014)
You can do it also with an Inner Join:
create table #MasterDates (D DATE)
go
insert into #MasterDates (D) VALUES
('2014-08-01'),
('2014-08-02'),
('2014-08-03'),
('2014-08-04'),
('2014-08-05'),
('2014-08-07'),
('2014-08-09')
go
create table #Employees (EmployeeID char(6), StartDate date, EndDate date)
go
INSERT INTO #Employees (EmployeeID, StartDate, EndDate)
values('003505', '2014-08-01', '2014-08-02'),
('003505', '2014-08-04', '2014-08-09')
go
select E.EmployeeID, MD.D
FROM #Employees E inner join #MasterDates MD ON E.StartDate <= MD.D and E.EndDate >= MD.D
go
--cleanup
DROP TABLE #MasterDates
DROP TABLE #Employees
The plans are identical:
SELECT e.EmployeeID, x.D
FROM #Employees e
CROSS APPLY (
SELECT md.D FROM #MasterDates MD WHERE md.D BETWEEN e.StartDate AND e.EndDate
) x
SELECT E.EmployeeID, MD.D
FROM #Employees E inner join #MasterDates MD ON md.D BETWEEN e.StartDate AND e.EndDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden