Expand “From” & “To” date columns to 1 row per day

问题:

Take the following sample data:

WITH SampleData AS (
    SELECT '8000213' AS EmployeeID, '2014-08-25 00:00:00.000' AS StartDate, '2014-08-31 00:00:00.000' AS EndDate, 28.5 AS HPW
    UNION ALL
    SELECT '8000213' AS EmployeeID, '2014-10-01 00:00:00.000' AS StartDate, NULL AS EndDate, 33 AS HPW
    UNION ALL
    SELECT '0003289' AS EmployeeID, '2014-04-25 00:00:00.0000' AS StartDate, '2014-04-30 00:00:00.000' AS EndDate, 36 AS HPW
    UNION ALL
    SELECT '0003289' AS EmployeeID, '2014-05-01 00:00:00.000' AS StartDate, NULL AS EndDate, 20 AS HPW
)
SELECT * FROM SampleData

How can we expand this data as follows (when there is no end date, assume current date):

I suspect some kind of recursion / CTE / tally table will be required here but cant quite get my head round it!

回答1:

There are lots of different ways to make a date generator; entire articles have been devoted to which one is fastest, but for simplicity's sake I'm going to tweak the one found here. I'd recommend doing some reading on the topic, and persisting a real date table in your database that you can use for queries like this (rather than generate one on the fly for each query you execute).

Step One: create a date table

Step Two: join each date within the table to an employee (note: I'm also filtering this to only show dates greater than the minimum start date in SampleData)

Step Three: join the date / distinct employees to your data to retrieve the HPW in force as of the given date.

SQL:

DECLARE @StartDate DATETIME = '2014-01-01 00:00:00.000'; -- this can be any date below the minimum StartDate

WITH SampleData AS (
    SELECT '8000213' AS EmployeeID, '2014-08-25 00:00:00.000' AS StartDate, '2014-08-31 00:00:00.000' AS EndDate, 28.5 AS HPW
    UNION ALL
    SELECT '8000213' AS EmployeeID, '2014-10-01 00:00:00.000' AS StartDate, NULL AS EndDate, 33 AS HPW
    UNION ALL
    SELECT '0003289' AS EmployeeID, '2014-04-25 00:00:00.000' AS StartDate, '2014-04-30 00:00:00.000' AS EndDate, 36 AS HPW
    UNION ALL
    SELECT '0003289' AS EmployeeID, '2014-05-01 00:00:00.000' AS StartDate, NULL AS EndDate, 20 AS HPW
),
SampleDateTable AS 
  (
    SELECT @StartDate AS myDate
    UNION ALL
    SELECT DATEADD(Day,1,myDate)
    FROM SampleDateTable
    WHERE DATEADD(Day,1,myDate) <=  GETDATE()
)
SELECT 
    EachEmployee.EmployeeID,
    a.myDate,
    SampleData.HPW
FROM 
    SampleDateTable a
     INNER JOIN 
      (
        SELECT EmployeeID, MIN(StartDate) MinStartDate
        FROM SampleData
        GROUP BY EmployeeID
      ) EachEmployee ON 
        a.MyDate >= EachEmployee.MinStartDate
     LEFT JOIN 
    SampleData ON
        EachEmployee.EmployeeID = SampleData.EmployeeID AND
        a.myDate >= SampleData.StartDate AND
        a.myDate <= ISNULL(SampleData.EndDate, GETDATE())
ORDER BY EachEmployee.EmployeeID DESC, a.MyDate
OPTION (MAXRECURSION 0)

回答2:

Alternatively you could simply continue CTE:

--Replace the enddate with "Getdate()" if you require expansion until "today/current"
Declare @DefaultEnddate Datetime = '2014-10-15 00:00:00.000'

;WITH SampleData AS (
SELECT '8000213' AS EmployeeID, '2014-08-25 00:00:00.000' AS StartDate, '2014-08-31 00:00:00.000' AS EndDate, 28.5 AS HPW
UNION ALL
SELECT '8000213' AS EmployeeID, '2014-10-01 00:00:00.000' AS StartDate, NULL AS EndDate, 33 AS HPW
UNION ALL
SELECT '0003289' AS EmployeeID, '2014-04-25 00:00:00.000' AS StartDate, '2014-04-30 00:00:00.000' AS EndDate, 36 AS HPW
UNION ALL
SELECT '0003289' AS EmployeeID, '2014-05-01 00:00:00.000' AS StartDate, NULL AS EndDate, 20 AS HPW
)
, Base as ( SELECT EmployeeID, StartDate = Convert(datetime, StartDate) , EndDate = Convert(datetime,isnull(EndDate,@DefaultEnddate)), HPW FROM SampleData)
, Expanded as (Select EmployeeID, StartDate as [Date], HPW , EndDate from Base
                union all
                Select EmployeeID, [Date] = [Date] + 1, HPW , EndDate from Expanded where [Date] < EndDate
        )
select EmployeeID,[Date], HPW
from Expanded 
Order by EmployeeID, [Date]
OPTION (MAXRECURSION 1000)