Practical Recursion – Part 2

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. Here in part 2 of the series is an example of how recursion can be used to seed a date table.

Also See…

Practical Recursion – Part 1

Recursive CTE vs While Loop – Performance Analysis

DECLARE

     @StartDate DATETIME

     , @EndDate DATETIME;

SELECT

     @StartDate = ‘1970/01/01’

     , @EndDate = ‘2055/12/31’;

WITH

[Dates] AS

(

     SELECT

          CAST(CONVERT(VARCHAR(8), @StartDate, 112) AS INT) AS [DateId]

          , CAST(@StartDate AS DATE) AS [Date]

          , CAST(@StartDate AS DATETIME) AS [DateTime]

          , DATEPART(YEAR, @StartDate) AS [Year]

          , DATEPART(QUARTER, @StartDate) AS [QuarterId]

          , ‘Q’ + CAST(DATEPART(QUARTER, @StartDate) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH, @StartDate) AS [MonthId]

          , DATENAME(MONTH, @StartDate) AS [Month]

          , DATEPART(DAY, @StartDate) AS [DayId]

          , DATEPART(DAYOFYEAR, @StartDate) AS [Day]

 

     UNION ALL

 

     SELECT

          CAST(CONVERT(VARCHAR(8), DATEADD(DAY, 1, D.[DateTime]), 112) AS INT) AS [DateId]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATE) AS [Date]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATETIME) AS [DateTime]

          , DATEPART(YEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Year]

          , DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS [QuarterId]

          , ‘Q’+CAST(DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [MonthId]

          , DATENAME(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [Month]

          , DATEPART(DAY,DATEADD(DAY, 1, D.[DateTime])) AS [DayId]

          , DATEPART(DAYOFYEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Day]

     FROM

          [Dates] AS D

     WHERE

          D.[DateTime] < @EndDate

)

–INSERT INTO

     [Date]

          ([DateId], [Date], [DateTime], [Year], [QuarterId], [Quarter], [MonthId], [Month], [DayId], [Day])

SELECT

    [DateId] AS [DateId]

    , [Date] AS [Date]

    , [DateTime] AS [DateTime]

    , [Year] AS [Year]

    , [QuarterId] AS [QuarterId]

    , [Quarter] AS [Quarter]

    , [MonthId] AS [MonthId]

    , [Month] AS [Month]

    , [DayId] AS [DayId]

    , [Day] AS [Day]

FROM

    [Dates] AS D

ORDER BY

    D.[DateId]

OPTION

    (MAXRECURSION 32767);

Advertisements

, , , , , , , , ,

  1. Practical Recursion – Part 3 | Brian Beswick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: