T-SQL End of Previous Week, Month, Quarter, Year

I recently had a requirement to capture periodic snapshots of an incident/activity type OLTP table into a fact table. I already had an accumulating snapshot type fact table where I was capturing the same facts on a daily basis. But, in addition to this, the client wanted the ability to perform period over period (weekly, monthly, quarterly, yearly) reporting.

My approach was simple enough and in case anyone was thinking about an overly complicated approach I figured I'd share . . .

After my daily load of the fact table I check to see if snapshot data exists for the prior week, month, quarter, and year. If any of these snapshots do not exist I simply do an INSERT selecting all data from the accumulating snapshot table and adding the As-Of date along with the level of periodicity (W,M,Q,Y).

I used 4 T-SQL if statements to determine if the pior period data exists and if it doesn't I call the INSERT.

Below are the statements used to capture the pior period as-of dates in an INT key format. The same format you would get converting a date to a VARCHAR using the 112 style (e.g. 20091231 for December 31st, 2009). I'll leave it up to you to apply this logic to your own INSERT statements.

--Previous end of month
DECLARE @EndOfWeekKey AS INT
SET @EndOfWeekKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(wk,-1,DATEADD(dd,-(DATEPART(dw, GETDATE()) - 7), GETDATE())),112) AS INT))

--Previous end of month
DECLARE @EndOfMonthKey AS INT
SET @EndOfMonthKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(d,-1,CAST(CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME)),112) AS INT))

--Previous end of quarter
DECLARE @EndOfQuarterKey AS INT
SET @EndOfQuarterKey = (
SELECT
    CASE
        WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '1231'
        WHEN MONTH(GETDATE()) IN (4,5,6) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0331'
        WHEN MONTH(GETDATE()) IN (7,8,9) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0630'
        WHEN MONTH(GETDATE()) IN (10,11,12) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0930'
END)

--Previous end of year
DECLARE @EndOfYearKey AS INT
SET @EndOfYearKey = (SELECT CAST(CAST(YEAR(GETDATE()) -1 AS VARCHAR) + '1231' AS INT))