14 January 2015

TSQL Calendar for Week Starting on Monday

The below SQL will generate a daily record for all days starting from next Sunday (end of financial week) through the last 6 months. It is built as a CTE so it is easy to include in any SQL statement. 


WITH
 E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1)--10^1 or 10 rows
,[Calendar] ([Day]) AS (
  SELECT
    CONVERT(DATE,DATEADD(DAY, 7 - DATEPART(WEEKDAY,GETDATE()-1) - (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)-1)),GETDATE()+1))
  FROM [E1] AS [A], [E1] AS [B], [E1] AS [C]) --10^3 or 1000 Days
--,[ReportingPeriods] AS (
  SELECT
    [Calendar].[Day] AS [ActiveDate]
    ,DATEPART(WEEKDAY, DATEADD(DAY, -1, [Calendar].[Day])) AS [DayOfWeekNumber]
    ,DATENAME(WEEKDAY, [Calendar].[Day]) AS [DayOfWeekName]
    ,CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0,DATEADD(DAY, -1, [Calendar].[Day])),0)) AS [StartOfWeek]-- offset by -1 for start of week on Mon
    ,CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0,DATEADD(DAY, 6, [Calendar].[Day])),0)) AS [EndOfWeek]-- offset by 6 for end of week on Sun
    ,DATEPART(DAY,DATEDIFF(DAY, 0, [Calendar].[Day] )/7 * 7)/7 + 1 AS [WeekOfMonth]
    ,DATEPART(WEEK,DATEADD(WEEK, DATEDIFF(WEEK, 0,DATEADD(DAY, -1, [Calendar].[Day])),0))-1 AS [WeekOfYear]
    ,DATEPART(MONTH,DATEADD(WEEK, DATEDIFF(WEEK, 0,DATEADD(DAY, -1, [Calendar].[Day])),0)) AS [MonthNumber]
    ,DATENAME(MONTH,DATEADD(WEEK, DATEDIFF(WEEK, 0,DATEADD(DAY, -1, [Calendar].[Day])),0)) AS [MonthName]
  FROM
    [Calendar]
  WHERE
    DATEDIFF(MONTH,0, [Calendar].[Day]) >= DATEDIFF(MONTH,0, DATEADD(MONTH, -5, GETDATE())) --include the last 6 months
--)

No comments:

Post a Comment

Blog Slideshow (all images from this blog)