Date Daily Dim Table Example

Date_Daily_Dim_Table.md

DROP TABLE IF EXISTS DimCustomer_Car_Status;

CREATE TABLE DimCustomer_Car_Status AS
WITH Joined_Data AS (
SELECT 
  o.RegistrationGUID
  ,o.RegistrationNo
  ,o.OwnerName
  ,o.FromDate AS CarRegistrationFromDate
  ,o.ToDate AS CarRegistrationToDate
  ,p.PolicyNo
  ,p.FromDate AS PolicyStartDate
  ,p.ToDate AS PolicyEndDate
  ,CASE
    WHEN p.FromDate >= o.FromDate THEN p.FromDate
    ELSE o.FromDate
  END AS StartDate
  ,CASE
    WHEN p.ToDate <= o.ToDate THEN p.ToDate
    ELSE o.ToDate
  END AS EndDate
FROM CarRegistration o
LEFT JOIN CarInsurancePolicy p
  ON o.RegistrationGUID = p.RegistrationGUID
  AND o.FromDate <= p.ToDate
  AND o.ToDate >= p.FromDate
)
SELECT
  RegistrationGUID || '_' || PolicyNo || '_' || StartDate AS Status_SK
  ,StartDate
  ,EndDate
  ,RegistrationGUID
  ,RegistrationNo
  ,CarRegistrationFromDate
  ,CarRegistrationToDate
  ,PolicyNo
  ,PolicyStartDate
  ,PolicyEndDate
 
FROM Joined_Data 
;

SELECT * FROM DimCustomer_Car_Status;