CROSS JOIN to time-slice history table

gistfile1.txt

-- Active status subscribers
-- Exposes Effective_Date as a pushdown predicate, use this to get only one possible value
REPLACE VIEW Subscriber_Snapshot AS 
SELECT Cal.Calendar_Date AS Effective_Date
       , Sub.Subscriber_Id
       , SSN.Status_Code
FROM Sys_Calendar.Calendar Cal
CROSS JOIN Subscriber Sub
INNER JOIN Sub_Status_History SSH
        ON Sub.Subscriber_ID = SSN.Subscriber_Id
       AND Cal.Calendar_Date BETWEEN SSH.Effective_Date AND SSH.End_Date
       AND SSH.Status_Code = 'Active';


--- ex
--- select ... 
--- from Subscriber_Snapshot Sub
---      ,SUB_PHONE_HISTORY ph
--- WHERE Sub.Effective_Date = CURRENT_DATE
---       AND Sub.Subscriber_Id = ph.Subscriber_Id
---       AND Sub.Effective_Date BETWEEN ph.Effective_Date and ph.Expiration_Date