Daily Date Island Gaps Example

Daily_Date_Islands_Gap.md

Daily Date Island Gaps Example

SELECT
  dt.FullDate
  ,c.RegistrationGUID
  ,c.RegistrationNo
  ,c.FromDate AS CarRegistrationFromDate
  ,c.ToDate AS CarRegistrationToDate
  ,p.PolicyNo
  ,p.FromDate AS PolicyStartDate
  ,p.ToDate AS PolicyEndDate
  --Enrich data by adding in flag which shows whether there was cover
  ,CASE
    WHEN p.PolicyNo IS NULL THEN 'N'
    ELSE 'Y'
  END AS Cover_YN
FROM CarRegistration c
INNER JOIN DimDate dt
ON dt.FullDate BETWEEN c.FromDate AND c.ToDate
LEFT JOIN CarInsurancePolicy p
  ON dt.FullDate BETWEEN p.FromDate AND p.ToDate
  AND c.RegistrationGUID = p.RegistrationGUID
;