DB2 : SQL: Change in granularity for SCD type-2 table

I have a order table in SCD type-2 like below (Delivery Date in Order granularity and it create history when when delivery date changes)

Order_Id Order_Status Order_Create_Dt Delivery_Dt Start_Date End_Date
O1       Open         20200303        20200321    20203001   20200306
O1       Open         20200303        20200320    20200307   20200311
O1       Open         20200303        20200318    20200312   99991231

​x
 
Order_Id Order_Status Order_Create_Dt Delivery_Dt Start_Date End_DateO1       Open         20200303        20200321    20203001   20200306O1       Open         20200303        20200320    20200307   20200311O1       Open         20200303        20200318    20200312   99991231​

Next I have a line table which is also in type-2 line below (it is having history when Quantity changes or description changes)

Order_Id Line_Item_Id Line_Item_Desc Quantity Start_Date End_Date
O1       L1           ABC            1        20200303   20200304
O1       L1           ABC            4        20200305   99991231
O1       L2           DEF            2        20200303   99991231
O1       L3           XYZ            3        20200303   99991231

 
Order_Id Line_Item_Id Line_Item_Desc Quantity Start_Date End_DateO1       L1           ABC            1        20200303   20200304O1       L1           ABC            4        20200305   99991231O1       L2           DEF            2        20200303   99991231O1       L3           XYZ            3        20200303   99991231​

Now as part of design change Delivery_Dt granularity changes from Order level to Line Item level and in a redesigned Line item table history need to be captured correctly for Line Item level change + Delivery Date change like below

Order_Id Line_Item_Id Line_Item_Desc Quantity Delivery_Dt Start_Date End_Date
O1       L1           ABC            1        20200321    20200303   20200304
O1       L1           ABC            4        20200321    20200305   20200306
O1       L2           DEF            2        20200321    20200303   20200306
O1       L3           XYZ            3        20200321    20200303   20200306  
O1       L1           ABC            4        20200320    20200307   20200311
O1       L2           DEF            2        20200320    20200307   20200311
O1       L3           XYZ            3        20200320    20200307   20200311  
O1       L1           ABC            4        20200318    20200312   99991231
O1       L2           DEF            2        20200318    20200312   99991231
O1       L3           XYZ            3        20200318    20200312   99991231

 
Order_Id Line_Item_Id Line_Item_Desc Quantity Delivery_Dt Start_Date End_DateO1       L1           ABC            1        20200321    20200303   20200304O1       L1           ABC            4        20200321    20200305   20200306O1       L2           DEF            2        20200321    20200303   20200306O1       L3           XYZ            3        20200321    20200303   20200306  O1       L1           ABC            4        20200320    20200307   20200311O1       L2           DEF            2        20200320    20200307   20200311O1       L3           XYZ            3        20200320    20200307   20200311  O1       L1           ABC            4        20200318    20200312   99991231O1       L2           DEF            2        20200318    20200312   99991231O1       L3           XYZ            3        20200318    20200312   99991231 ​

Can this be achieved simply through a SQL statement using the existing Order and Line Item tables ?

I am trying this in DB2 database.

Answer

This could be a solution for you:

SELECT t1.Order_Id, t2.Line_Item_Id, t2.Line_Item_Desc, t2.Quantity, t1.Delivery_Dt
     , max(t1.Start_Date, t2.Start_Date) AS Start_Date
     , min(t1.End_Date, t2.End_Date) AS End_Date
  FROM t2 
 INNER JOIN T1 
    ON t2.Order_Id = t1.Order_Id
       AND t1.Start_Date <= t2.End_Date 
       AND t1.End_Date >= t2.Start_Date 
 WHERE max(t1.Start_Date, t2.Start_Date) <> min(t1.End_Date, t2.End_Date)

 
SELECT t1.Order_Id, t2.Line_Item_Id, t2.Line_Item_Desc, t2.Quantity, t1.Delivery_Dt     , max(t1.Start_Date, t2.Start_Date) AS Start_Date     , min(t1.End_Date, t2.End_Date) AS End_Date  FROM t2  INNER JOIN T1     ON t2.Order_Id = t1.Order_Id       AND t1.Start_Date <= t2.End_Date        AND t1.End_Date >= t2.Start_Date  WHERE max(t1.Start_Date, t2.Start_Date) <> min(t1.End_Date, t2.End_Date) ​

There is a brilliant blog about that topic: „Fun with Date Ranges“ by Jon Maenpaa

You can also do that with Db2 Temporal Tables offering time travel SQL in addition.

Advertisement

Answer