0

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  
1

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          |
  • Thanks, Scott! Worked well and ran fast enough for out needs. – Kim Wennerberg Feb 8 at 19:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.