SQL - Constructing an SCD2 type dimension from overlapping periods

user_1496440 0
Viewed 38

I have data like this:

GroupId DateFrom   DateTo     value_
Gr1     2022-03-01 2022-08-01 10
Gr2     2022-01-01 2022-12-31 20
Gr3     2022-01-01 2022-12-31 30

I'm trying to construct an SCD2 type dimension by doing an unpivot on data above

WITH UnPivoted AS (SELECT 'Gr1' AS GroupId, '2022-03-01' AS DateFrom, '2022-08-01' AS DateTo, 10 as value_ UNION ALL
                 SELECT 'Gr2', '2022-01-01', '2022-12-31', 20 UNION ALL
                 SELECT 'Gr3', '2022-01-01', '2022-12-31', 30
                 )

SELECT DateFrom, DateTo, SUM([Gr1]) Gr1, SUM([Gr2]) Gr2, SUM([Gr3]) Gr3
FROM UnPivoted
PIVOT (
    SUM(value_) FOR GroupId IN ([Gr1],[Gr2],[Gr3])
    ) pvt
GROUP BY DateFrom, DateTo

with result:

DateFrom   DateTo     Gr1  Gr2  Gr3
2022-03-01 2022-08-01 10   NULL NULL
2022-01-01 2022-12-31 NULL 20   30

But, as you can see, date ranges are not identical so my GROUP BY does not work. And there is an overlap in date ranges so output is not correct.

I would like to get this result instead:

DateFrom   DateTo     Gr1  Gr2  Gr3
2022-01-01 2022-03-01      20   30
2022-03-01 2022-08-01 10   20   30
2022-08-01 2022-12-31      20   30

The best approach that I can come up with is to get all distinct values of DateFrom and DateTo and go through intervals between them one by one, constructing a new row for each interval.

Is there an easier way of getting the desired result?

sql-server sql
1 Answers

In case someone else has the same situation, script below works. It also has some additional logic to adjust end dates so they do not overlap with start dates.

input (Unpivoted CTE):

GroupId DateFrom   DateTo     value_
Gr1     2022-03-01 2022-08-01 10
Gr2     2022-01-01 2022-12-31 20
Gr3     2022-01-01 2022-12-31 30

script:

WITH UnPivoted AS (SELECT 'Gr1' AS GroupId, CAST('2022-03-01' AS date) AS DateFrom, CAST('2022-08-01' AS date) AS DateTo, 10 as value_ UNION ALL
                    SELECT 'Gr2', '2022-01-01', '2022-12-31', 20 UNION ALL
                    SELECT 'Gr3', '2022-01-01', '2022-12-31', 30
                    )
,UniqueDateRanges AS (
    SELECT DISTINCT DateFrom
    FROM UnPivoted
    UNION
    SELECT DISTINCT DATEADD(d,1,DateTo)
    FROM UnPivoted
)
,DateIntervals_SCD2 AS (
    SELECT DateFrom
          ,CAST(NULLIF(LEAD(DateFrom,1,NULL) OVER(PARTITION BY '1' ORDER BY DateFrom),NULL) AS date) AS DateTo1
          ,CAST(DATEADD(d,-1,NULLIF(LEAD(DateFrom,1,NULL) OVER(PARTITION BY '1' ORDER BY DateFrom),NULL)) AS date) AS DateTo1_adjusted
    FROM UniqueDateRanges
)
,Dataset_Fixed_SCD2 AS (
    SELECT di.DateFrom, DateTo1_adjusted AS DateTo, up.GroupId, up.value_
    FROM DateIntervals_SCD2 di
    LEFT JOIN UnPivoted up ON di.DateFrom BETWEEN up.DateFrom AND up.DateTo AND DateTo1_adjusted BETWEEN up.DateFrom AND up.DateTo
    WHERE DateTo1_adjusted IS NOT NULL
)
SELECT *
FROM Dataset_Fixed_SCD2
PIVOT (
    SUM(value_) FOR GroupId IN ([Gr1],[Gr2],[Gr3])
    ) pvt

output:

DateFrom   DateTo     Gr1  Gr2 Gr3
2022-01-01 2022-02-28      20  30
2022-03-01 2022-08-01 10   20  30
2022-08-02 2022-12-31      20  30
user_14964400
Related