sql - 表拆分中的 T-SQL 日期范围并将单个日期添加到表中

Question

感谢您寻求帮助。我有一张这样的 table 。让我们称之为 TableA

[Id] [CHAR](10) NOT NULL,
[DType] [SMALLINT] NOT NULL,
[PType] [CHAR](1) NOT NULL,
[Period] [INT] NOT NULL,
[FromDate] [SMALLDATETIME] NOT NULL,
[ToDate] [SMALLDATETIME] NOT NULL,
[Highval] [DECIMAL](19, 4) NULL,

主键是Id, DType, PType, Period, FromDate

里面有这样的数据:

Id          DType   PType   Period  FromDate    ToDate      Highval
-------------------------------------------------------------------    
000000000G  1       A       2015    2014-11-02  2014-11-04  0.4800
000000000G  1       A       2015    2014-11-01  2014-11-01  1.2860
000000000G  1       A       2015    2014-10-28  2014-10-31  1.2290
000000000K  4       2       2015    2014-12-17  2014-12-20  2.5800
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700
000000000K  4       3       2015    2014-11-14  2014-12-14  2.7200

我需要一种方法来拆分日期范围，任何大于或等于 FromDate 且小于或等于 ToDate 的值，我需要将它们拆分到另一个名为 IndividualDate 的列到同一个表中。基本上，在给定的日期范围内，如果日期介于 FromDate 和 ToDate 之间，则将日期写入另一个名为 IndividualDate 的列中。对于给定的 Id、DType、PType、PEnd、FromDate，FromDate 和 ToDate 字段日期范围没有重复项。因此，日期范围是完全分开的。上述示例数据的最终输出表(临时表也可以)如下所示。

Id          DType   PType   Period  FromDate    ToDate      Highval IndividualDate
----------------------------------------------------------------------------------
000000000G  1       A       2015    2014-11-02  2014-11-04  0.4800  2014-11-02 
000000000G  1       A       2015    2014-11-02  2014-11-04  0.4800  2014-11-03 
000000000G  1       A       2015    2014-11-02  2014-11-04  0.4800  2014-11-04 
000000000G  1       A       2015    2014-11-01  2014-11-01  1.2860  2014-11-01 
000000000G  1       A       2015    2014-10-28  2014-10-31  1.2290  2014-10-28 
000000000G  1       A       2015    2014-10-28  2014-10-31  1.2290  2014-10-29 
000000000G  1       A       2015    2014-10-28  2014-10-31  1.2290  2014-10-30 
000000000G  1       A       2015    2014-10-28  2014-10-31  1.2290  2014-10-31 
000000000K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-17 
000000000K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-18 
000000000K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-19 
000000000K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-20 
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-15      
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-16 
000000000K  4       3       2015    2014-11-14  2014-12-14  2.7200  2014-12-14

如有任何建议，我们将不胜感激。提前谢谢你。

Answer 1

这个使用Tally Table。供引用:http://www.sqlservercentral.com/articles/T-SQL/62867/

;WITH e1(N) AS(
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
)
,e2(N) AS(SELECT 0 FROM e1 a, e1 b)
,e4(N) AS(SELECT 0 FROM e2 a, e2 b)
,Tally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, FromDate, ToDate) + 1) FROM TableA) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM e4
)
SELECT
    a.Id,
    a.Dtype,
    a.Ptype,
    a.Period,
    FromDate = CONVERT(VARCHAR(10), a.FromDate, 120),
    ToDate = CONVERT(VARCHAR(10), a.ToDate, 120),
    IndividualDate = CONVERT(VARCHAR(10), DATEADD(DAY, t.N -1, a.FromDate), 120)
FROM TableA a
CROSS JOIN Tally t
WHERE   
    t.N - 1 <= DATEDIFF(DAY, a.FromDate, a.ToDate)
ORDER BY a.Id, a.DType, a.PType, a.Period, a.Highval, IndividualDate