Date_Islands_Example.md

Date Islands Example

# Customer Contract and Status Dimension Table
CREATE TABLE Dim_Cust_Contract_Status AS (
SELECT 
    ROW_NUMBER() OVER (ORDER BY K.Customer_ID) AS Customer_Contract_Status_SK,
    K.Customer_ID,
    K.Contract_GUID,
    K.Contract_Start_Date,
    K.Contract_End_Date,
    C.Status,
    C.Valid_From,
    C.Valid_To,
    CASE
        WHEN C.Valid_From >= K.Contract_Start_Date THEN C.Valid_From
        ELSE K.Contract_Start_Date
      END AS From_Date, 
      CASE
        WHEN C.Valid_To <= K.Contract_End_Date THEN C.Valid_To
        ELSE K.Cntract_End_Date
    END AS To_Date
  FROM Dim_Customer_Contract K
  LEFT JOIN Dim_Customer C 
    ON C.Valid_To >= K.Contract_Start_Date
    AND C.Valid_From <= K.Contract_End_Date
);

SELECT
    Customer_Contract_Status_SK
    P.Product,
    P.Purchase_Date
FROM Dim_Customer_Contract_status D
  LEFT JOIN Fact_Purchase_Orders P
    ON P.Purchase_Date BETWEEN D.From_Date AND D.To_Date;