T-SQL date range in a table split and add the indi

问题:

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.7200

I 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-14

Any suggestions would be greatly appreciated. Thank you in advance.

回答1:

This 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

回答2:

Use Recursive CTE to generate dates

DECLARE @maxdate DATE

SELECT @maxdate = Max(todate)
FROM   Yourtable;

WITH cte
     AS (SELECT Min(fromdate) Invidualdate
         FROM   Yourtable 
         UNION ALL
         SELECT Dateadd(dd, 1, Invidualdate)
         FROM   cte
         WHERE  Invidualdate < @maxdate)
SELECT id,
       DType,
       PType,
       Period,
       FromDate,
       ToDate,
       Highval,
       Invidualdate
FROM   cte a
       JOIN Yourtable b
         ON a.Invidualdate BETWEEN b.FromDate AND b.ToDate 
Option (maxrecursion 0)

Or create calender table which is a better way

create table calender(Individualdate date)

insert into calender values('2001-01-01'),....,('2020-12-31')


SELECT id,
       DType,
       PType,
       Period,
       FromDate,
       ToDate,
       Highval,
       Individualdate
FROM   calender a
       JOIN yourtable b
         ON a.Individualdate BETWEEN b.FromDate AND b.ToDate