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?

This is possible but unlikely to be useful -- what is your end goal? I expect there is an easier way to get there. — Hogan
Why would you want to return all that data? If an employee has been employed for 10 years you would return 3652 rows. You should iterate in code. — Paul Fleming
What is the data type of the StartDate and EndDate columns? CHAR/VARCHAR(6) or DATE? DATE or DATETIME would be easier to work with... — Bryan
I am building a fact table to derive a measure in SSAS. The measure is 'average time of employment'. I know this query would generate a lot of records but this is how fact tables work. Eventually, the aggregates will go into a cube ad the end user will have the ability to drill-down into the date period - year, month, week, day. — Thracian
If you're required to use Analysis Services, then you need to derive a star schema from your dataset, create a cube from that schema, Extract/Transform/Load your dataset into the schema using some form of ETL tool or process and then query from your dashboard using MDX. A simple SQL query isn't the answer (it could be if you're actually building the dashboard from scratch). — Ezequiel Muns

Taryn Taryn · Accepted Answer · 2013-01-15T20:21:48

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 |

SQL - building a fact table with calendar day granularity

2 Answers