I have a list of employees. I need to create output which is one row per date in a date range for each employee. The date range will always be the last 15 days, so it is expected to have 15 rows for each employee. A "few thousand" employees. This is SQL Server.
SAMPLE SOURCE EMP TABLE:
EMP1
EMP2
RESULT:
EMP1, JAN 1
EMP1, JAN 2
EMP1, JAN 3
EMP2, JAN 1
EMP2, JAN 2
EMP2, JAN 3
Here is an example of using recursion to build a common expression date table and then cross join to the employee table.
--demo setup
declare @EmpTable table (EmpName varchar(50))
insert into @EmpTable(EmpName) values('emp1'),('emp2')
--use recursion to build a common table expression date table
;WITH dates ([Date])
AS (
SELECT convert(DATE, '2019-01-01') AS [Date] -- Put the start date here
UNION ALL
SELECT dateadd(day, 1, [Date])
FROM dates
WHERE [Date] < '2019-01-15' -- Put the end date here
)
SELECT e.empname
,substring(DATENAME(month, ([date])), 1, 3) + ' ' + DATENAME(day, ([date])) AS [DateDescription]
FROM dates d
CROSS JOIN @EmpTable e
ORDER BY empname, [date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!
| empname | DateDescription |
|---------|-----------------|
| emp1 | Jan 1 |
| emp1 | Jan 2 |
| emp1 | Jan 3 |
| emp1 | Jan 4 |
| emp1 | Jan 5 |
| emp1 | Jan 6 |
| emp1 | Jan 7 |
| emp1 | Jan 8 |
| emp1 | Jan 9 |
| emp1 | Jan 10 |
| emp1 | Jan 11 |
| emp1 | Jan 12 |
| emp1 | Jan 13 |
| emp1 | Jan 14 |
| emp1 | Jan 15 |
| emp2 | Jan 1 |
| emp2 | Jan 2 |
| emp2 | Jan 3 |
| emp2 | Jan 4 |
| emp2 | Jan 5 |
| emp2 | Jan 6 |
| emp2 | Jan 7 |
| emp2 | Jan 8 |
| emp2 | Jan 9 |
| emp2 | Jan 10 |
| emp2 | Jan 11 |
| emp2 | Jan 12 |
| emp2 | Jan 13 |
| emp2 | Jan 14 |
| emp2 | Jan 15 |
