Tim

Tim Kent's Blog

Type 2 SCDs - Calculating End Date when you only have Effective Date

Here's a quick one for creating an end date column when you only have an effective date column in your data source to work with.  The secret is to join the table to itself with a greater than join on the effective date.  You then use the effective date from your joined table to give you your end date.

SELECT     
    DT1.AccountID 
    ,DT1.EffectiveDate
    --Add a default end date for current record and subtract a day so the end date is one day before the next start date  
    ,ISNULL(DATEADD(d,-1, DT2.EffectiveDate), '31/12/2099') AS EndDate 
    ,DT1.Price
FROM         
    dbo.PriceTable AS DT2 
    RIGHT OUTER JOIN
        dbo.PriceTable AS DT1 
        DT2.AccountID = DT1.AccountID 
        AND
        DT2.EffectiveDate > DT1.EffectiveDate
Loading