Consider the following table that gives the validity dates for which an Item
is associated to something called a Demand_Unit
, and a certain Market
:
Item_ID | Start_Date | End_Date | Demand_Unit | Market |
---|---|---|---|---|
X | 2020-06-01 | 2020-09-30 | A | GREECE |
X | 2020-11-01 | 2021-01-01 | A | BELGIUM |
X | 2021-01-01 | 2023-12-31 | A | USA |
Y | 2021-01-01 | 2021-02-01 | B | PORTUGAL |
Y | 2021-02-01 | 2021-06-07 | B | FRANCE |
Y | 2021-06-07 | 2022-01-03 | B | ITALY |
Y | 2022-01-03 | 2023-12-31 | B | SPAIN |
Y | 2024-01-01 | 2025-12-31 | B | UKRAINE |
Z | 2021-05-01 | 2021-06-30 | C | USA |
W | 2021-02-01 | 2021-05-30 | D | SWEDEN |
W | 2023-12-31 | 2025-05-30 | D | DANEMARK |
U | 2021-01-04 | 2022-02-07 | E | TUNISIA |
U | 2022-02-07 | 2025-01-05 | (null) | MOROCCO |
Important note:
You can never have overlapping dates because basically when one Market closes another opens. But it is possible to have non-contiguous periods (like forItem
X for example)
Now consider the following table that gives the Classification
s of a Demand_Unit
throughout time:
Demand_Unit | Start_Date | End_Date | Classification |
---|---|---|---|
A | 2021-01-01 | 2021-04-05 | N+ |
A | 2021-04-05 | 2023-12-31 | K- |
B | 2021-03-08 | 2021-07-26 | N+ |
B | 2021-07-26 | 2022-11-30 | L- |
C | 2021-01-01 | 2023-12-31 | N- |
D | 2021-06-01 | 2023-12-31 | K+ |
E | 2021-08-02 | 2022-01-03 | N+ |
E | 2022-01-03 | 2025-01-06 | C- |
Important note:
You can only have contiguous periods here. This table is extracted from a Slowly changing dimension.
The goal is to 'slice' the first table to associate to each Item
its Classification
, while taking into account the time periods. The result should look like this:
Item_ID | Demand_Unit | Market | Item_Classification_Dt_Begin | Item_Classification_Dt_End | Item_Classification |
---|---|---|---|---|---|
U | E | TUNISIA | 2021-01-04 | 2021-08-02 | (null) |
U | E | TUNISIA | 2021-08-02 | 2022-01-03 | N+ |
U | E | TUNISIA | 2022-01-03 | 2022-02-07 | C- |
U | (null) | MOROCCO | 2022-02-07 | 2025-01-05 | (null) |
W | D | SWEDEN | 2021-02-01 | 2021-05-30 | (null) |
W | D | DANEMARK | 2023-12-31 | 2023-12-31 | K+ |
W | D | DANEMARK | 2023-12-31 | 2025-05-30 | (null) |
X | A | GREECE | 2020-06-01 | 2020-09-30 | (null) |
X | A | BELGIUM | 2020-11-01 | 2021-01-01 | (null) |
X | A | USA | 2021-01-01 | 2021-04-05 | N+ |
X | A | USA | 2021-04-05 | 2023-12-31 | K- |
Y | B | PORTUGAL | 2021-01-01 | 2021-02-01 | (null) |
Y | B | FRANCE | 2021-02-01 | 2021-03-08 | (null) |
Y | B | FRANCE | 2021-03-08 | 2021-06-07 | N+ |
Y | B | ITALY | 2021-06-07 | 2021-07-26 | N+ |
Y | B | ITALY | 2021-07-26 | 2022-01-03 | L- |
Y | B | SPAIN | 2022-01-03 | 2022-11-30 | L- |
Y | B | SPAIN | 2022-11-30 | 2023-12-31 | (null) |
Y | B | UKRAINE | 2024-01-01 | 2025-12-31 | (null) |
Z | C | USA | 2021-05-01 | 2021-06-30 | (null) |
Z | C | USA | 2021-05-01 | 2021-06-30 | N- |
These are some examples with 'tricky' cases (Items X and Y) and a 'simple' case (Item Z) which has its dates completely included in the classifications period, + other cases to try and test possibilities I could think of...
I'm working on a solution that uses LAG()
LEAD()
and a three-part UNION
, but I'm afraid I'm forgetting something and the original table has hundreds of thousands of rows.
I'm not trying to have someone do my work here, I would just like your opinions on how should this be properly done.
Here's my solution so far, it looks like it works but I don't find it elegant and I'm sure the second and third UNION
block can be simplified, but I just can't see straight anymore.
;WITH CLASSIFICATIONS_LAG_LEAD AS
(
SELECT
Demand_Unit
,Start_Date
,End_Date
,Classification
,LAG(End_Date) OVER (PARTITION BY Demand_Unit ORDER BY Start_Date) AS LAGD
,LEAD(Start_Date) OVER (PARTITION BY Demand_Unit ORDER BY Start_Date ) AS LEADD
FROM Classifications
)
-- This first block handles Item periods that are not covered by classifications periods
-- and/or the NULL classif portion of Item periods that are partially covered by classifications periods
SELECT
T1.Item_ID
,T1.Demand_Unit
,T1.Market
,CASE
WHEN T2.End_Date > T1.Start_Date
AND T2.End_Date <= T1.End_Date
THEN T2.End_Date
ELSE T1.Start_Date
END AS Item_Classification_Dt_Begin
,CASE
WHEN T2.Start_Date > T1.Start_Date
AND T2.Start_Date <= T1.End_Date
THEN T2.Start_Date
ELSE T1.End_Date
END AS Item_Classification_Dt_End
,NULL AS Item_Classification
FROM Items T1
INNER JOIN CLASSIFICATIONS_LAG_LEAD T2
ON T1.Demand_Unit = T2.Demand_Unit
AND (
(
LAGD IS NULL
AND T1.Start_Date < T2.Start_Date
)
OR
(
LEADD IS NULL
AND T1.End_Date > T2.End_Date
)
)
UNION
-- The following block handles Items periods that are (at least partially)
-- contained within the Classification period, and checks which DT_BEGIN to use
SELECT
T1.Item_ID
,T1.Demand_Unit
,T1.Market
,CASE
WHEN T1.Start_Date < T2.Start_Date THEN T2.Start_Date
ELSE T1.Start_Date
END AS Item_Classification_Dt_Begin
,T1.End_Date AS Item_Classification_Dt_End
,T2.Classification AS Item_Classification
FROM Items T1
INNER JOIN Classifications T2
ON T1.Demand_Unit = T2.Demand_Unit
AND T1.End_Date <= T2.End_Date
AND T1.End_Date > T2.Start_Date
UNION
-- The following block handles Items periods that are (at least partially)
-- contained within the Classification period, and checks which DT_END to use
SELECT
T1.Item_ID
,T1.Demand_Unit
,T1.Market
,T1.Start_Date AS Item_Classification_Dt_Begin
,CASE
WHEN T1.End_Date > T2.End_Date THEN T2.End_Date
ELSE T1.End_Date
END AS Item_Classification_Dt_End
,T2.Classification AS Item_Classification
FROM Items T1
INNER JOIN Classifications T2
ON T1.Demand_Unit = T2.Demand_Unit
AND T1.Start_Date < T2.End_Date
AND T1.Start_Date >= T2.Start_Date
ORDER BY Item_ID, 4
Many thanks in advance
Edit : last minute fixes in my solution
Classificaitons
should be for C, not A.