展开“来自” & “至”日期列到该范围内每天1行 [英] Expand "From" & "To" date columns to 1 row per day within that range

问题描述

获取以下示例数据：

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):

我怀疑此处需要某种递归/ CTE /理货表格，但不能完全让我反感

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

推荐答案

创建日期生成器有很多不同的方法。整篇文章都专门讨论了哪一篇最快，但是为了简单起见，我将调整此处。建议您阅读有关该主题的内容，并将真实的日期表持久保存在数据库中，以用于类似这样的查询（而不是为您执行的每个查询动态生成一个日期表）。

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

第二步：将表中的每个日期与一个员工（注意：我也将其过滤为仅显示大于SampleData中最小开始日期的日期）

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)

这篇关于展开“来自” & “至”日期列到该范围内每天1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！