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