2

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 for Item X for example)

Now consider the following table that gives the Classifications 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

Here is a SQLFiddle

Many thanks in advance

Edit : last minute fixes in my solution

Improve this question
7
  • Can you edit your question and post your working query?
    – dwhitemv
    Oct 27, 2021 at 16:46
  • You have a typo in your fiddle - I believe the last row inserted into Classificaitons should be for C, not A.
    – user212533
    Oct 27, 2021 at 16:53
  • @dwhitemv : Done, I just don't find my solution elegant and didn't want to influence other answers.
    – Yass T
    Oct 31, 2021 at 10:52
  • @bbaird you were right, thank you ! fixed the fiddle
    – Yass T
    Oct 31, 2021 at 10:53
  • I tagged this with gaps-and-islands since it seems like an interesting reverse case of putting the gaps back in. Take a look at the tagged posts for other tactics. Your strategy looks sound. Minor quibbles would be to use GREATEST() and LEAST() instead of a couple of CASEs and see if UNION ALL would work instead of the deduped UNION.
    – dwhitemv
    Nov 1, 2021 at 15:02

1 Answer 1

Reset to default
0

The following solution works better for edge cases. It takes advantage of the fact that

You can only have contiguous periods in the Classifications table

.

;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
)
,Items_Numbered AS
(
SELECT
Item_ID
,Start_Date
,End_Date
,Demand_Unit
,Market
FROM Items
)
, Slices AS 
(
-- The following block takes care of Item periods that starts before the First Classif 
-- period (or finishes after the Last CLassif period), and it specifies NULL Classif
SELECT 
I1.Item_ID
,I1.Demand_Unit 
,I1.Market
,CASE
   WHEN C1.Demand_Unit IS NULL THEN I1.Start_Date
   WHEN C1.End_Date < I1.End_Date AND I1.Start_Date >= C1.Start_Date THEN C1.End_Date
   ELSE I1.Start_Date                 
 END                           AS Item_Classification_Dt_Begin
,CASE
   WHEN C1.Demand_Unit IS NULL THEN I1.End_Date
   WHEN C1.End_Date < I1.End_Date AND I1.Start_Date >= C1.Start_Date THEN I1.End_Date
   --AND I1.Start_Date >= C1.Start_Date
   ELSE C1.Start_Date
 END                           AS Item_Classification_Dt_End
,CAST(NULL AS NVARCHAR(3))                          AS Item_Classification
FROM Items_Numbered I1
INNER JOIN CLASSIFICATIONS_LAG_LEAD C1 
    ON I1.Demand_Unit = C1.Demand_Unit
    AND ( 
      (C1.Start_Date >= I1.Start_Date AND C1.Start_Date < I1.End_Date AND LAGD IS NULL)
      OR
      (C1.End_Date >= I1.Start_Date AND C1.End_Date < I1.End_Date AND LEADD IS NULL)
    )

UNION ALL
--The following block basically takes care of all items periods that partially overlap Classif periods
SELECT
I1.Item_ID
,I1.Demand_Unit 
,I1.Market
,CASE
   WHEN C1.End_Date < I1.End_Date AND I1.Start_Date >= C1.Start_Date THEN I1.Start_Date
   ELSE C1.Start_Date                 
 END                           AS Item_Classification_Dt_Begin
,CASE
   WHEN C1.End_Date < I1.End_Date THEN C1.End_Date
   ELSE I1.End_Date
 END                           AS Item_Classification_Dt_End
,C1.Classification             AS Item_Classification
FROM Items_Numbered I1
INNER JOIN CLASSIFICATIONS_LAG_LEAD C1 
    ON I1.Demand_Unit = C1.Demand_Unit
    AND ( 
      (C1.Start_Date >= I1.Start_Date AND C1.Start_Date < I1.End_Date)
      OR
      (C1.End_Date >= I1.Start_Date AND C1.End_Date < I1.End_Date)
    )

UNION ALL

-- The following block takes care of Item periods that are completely included inside a Classif period
SELECT  
I1.Item_ID
,I1.Demand_Unit 
,I1.Market
,I1.Start_Date                 AS Item_Classification_Dt_Begin
,I1.End_Date                   AS Item_Classification_Dt_End
,C1.Classification             AS Item_Classification
FROM Items_Numbered I1
INNER JOIN CLASSIFICATIONS_LAG_LEAD C1 
    ON I1.Demand_Unit = C1.Demand_Unit
    AND I1.Start_Date >= C1.Start_Date 
    AND I1.End_Date < C1.End_Date

)

SELECT
Item_ID,
Demand_Unit,
Market,
Item_Classification_Dt_Begin,
Item_Classification_Dt_End,
MAX(Item_Classification) AS Item_Classification
FROM Slices
GROUP BY 
Item_ID,
Demand_Unit,
Market,
Item_Classification_Dt_Begin,
Item_Classification_Dt_End

UNION ALL
--The following block takes care of all the Item periods that have no match with the classifs
--(either because the Item is not linked to a DU, or because there is no Classif for the whole time period)
-- Since we used INNER JOIN in the previous CTE, we must include these rows back in this block...
SELECT
I1.Item_ID
,I1.Demand_Unit 
,I1.Market
,I1.Start_Date AS Item_Classification_Dt_Begin
,I1.End_Date  AS Item_Classification_Dt_End
,NULL            AS Item_Classification
FROM Items_Numbered I1
LEFT JOIN CLASSIFICATIONS_LAG_LEAD C1 
    ON I1.Demand_Unit = C1.Demand_Unit
    AND ( 
      (C1.Start_Date >= I1.Start_Date AND C1.Start_Date < I1.End_Date)
      OR
      (C1.End_Date >= I1.Start_Date AND C1.End_Date < I1.End_Date)
    )
WHERE C1.Demand_Unit IS NULL

ORDER BY Item_ID, Item_Classification_Dt_Begin
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.