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
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
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
Can this be achieved simply through a SQL statement using the existing Order and Line Item tables ?
I am trying this in DB2 database.
Advertisement
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)
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.