SQL - building a fact table with calendar day granularity

Question

I have a dataset (DATASET1) that lists all employees with their Dept IDs, the date they started and the date they were terminated.

I'd like my query to return a dataset in which every row represents a day for each employee stayed employed, with number of days worked (Start-to-Date).

How do I this query? Thanks for your help, in advance.

DATASET1

DeptID     EmployeeID   StartDate   EndDate
--------------------------------------------
001        123           20100101   20120101   
001        124           20100505   20130101

DATASET2

DeptID     EmployeeID    Date       #ofDaysWorked
--------------------------------------------
001        123           20100101   1
001        123           20100102   2
001        123           20100103   3
001        123           20100104   4
....       ....          ........   ...

EIDT: My goal is to build a fact table which would be used to derive measures in SSAS. The measure I am building is 'average length of employment'. The measure will be deployed in a dashboard and the users will have the ability to select a calendar period and drill-down into month, week and days. That's why I need to start with such a large dataset. Maybe I can accomplish this goal by using MDX queries but how?

Answer 1

You can use a recursive CTE to perform this:

;with data (deptid, employeeid, inc_date, enddate) as
(
  select deptid, employeeid, startdate, enddate
  from yourtable
  union all
  select deptid, employeeid,
    dateadd(d, 1, inc_date),
    enddate
  from data
  where dateadd(d, 1, inc_date) <= enddate
) 
select deptid,
  employeeid,
  inc_date,
  rn NoOfDaysWorked
from
(
  select deptid, employeeid,
    inc_date, 
    row_number() over(partition by deptid, employeeid
                      order by inc_date) rn
  from data
) src
OPTION(MAXRECURSION 0)

See SQL Fiddle with Demo

The result is similar to this:

| DEPTID | EMPLOYEEID |       DATE | NOOFDAYSWORKED |
-----------------------------------------------------
|      1 |        123 | 2010-01-01 |              1 |
|      1 |        123 | 2010-01-02 |              2 |
|      1 |        123 | 2010-01-03 |              3 |
|      1 |        123 | 2010-01-04 |              4 |
|      1 |        123 | 2010-01-05 |              5 |
|      1 |        123 | 2010-01-06 |              6 |
|      1 |        123 | 2010-01-07 |              7 |
|      1 |        123 | 2010-01-08 |              8 |
|      1 |        123 | 2010-01-09 |              9 |
|      1 |        123 | 2010-01-10 |             10 |
|      1 |        123 | 2010-01-11 |             11 |
|      1 |        123 | 2010-01-12 |             12 |