感谢您的帮助。 我有一张这样的桌子。 让我们称之为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任何建议将不胜感激。 先谢谢你。
Thank you for looking to help. I have a table like this. Lets call it 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,The Primary Key is Id, DType, PType, Period, FromDate
There is data in it like this:
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.7200I need a way to split the date ranges, anything greater than or equal to FromDate and less than or equal to ToDate, I need to split them into another column called IndividualDate into the same table. Basically, in the given date ranges, if the date exists between the FromDate and ToDate write the date in another column called IndividualDate. For a given Id, DType, PType, PEnd, FromDate, the FromDate and ToDate field date ranges do not have duplicates. So, the date ranges are cleanly separated. The final output table(temporary table is fine) for the above sample data would look like this.
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-14Any suggestions would be greatly appreciated. Thank you in advance.
最满意答案
这个使用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, IndividualDateThis one uses a Tally Table. For reference: 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更多推荐
发布评论