sql - 在该范围内每天将 "From"和 "To"日期列扩展到 1 行

Question

获取以下示例数据:

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

我们如何按如下方式扩展此数据(如果没有结束日期，则假定为当前日期):

我怀疑这里需要某种递归/CTE/计数表，但我无法完全理解它!

Answer 1

制作日期生成器的方法有很多种；整篇文章都致力于哪一个是最快的，但为了简单起见，我将调整找到的那个 here .我建议您阅读一些有关该主题的文章，并在您的数据库中保留一个真实的日期表，您可以将其用于这样的查询(而不是为您执行的每个查询即时生成一个)。

第一步:创建日期表

第二步:将表中的每个日期连接到一个员工(注意:我也在过滤它以仅显示大于 SampleData 中的最小开始日期的日期)

第三步:将日期/不同员工加入您的数据，以检索截至给定日期有效的 HPW。

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)