Posts Tagged Range

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 Comment

Practical Recursion – Part 1

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. To kick off the series, this post is a recursive CTE that I’ve used in a number of situations for compiling transactional data into dimensional records. The example is based around member eligibility, but it can be easily modified to convert any transactional data into dimensional data.

Also See...

Practical Recursion – Part 2
Recursive CTE vs While Loop – Performance Analysis

 

WITH

[MemberEligibilityHistory] AS

(

     SELECT

          M.[MemberId] AS [MemberId]

          , M.[Member] AS [Member]

          , M.[FromDate] AS [MemberEligibilityPeriodStartDate]

          , M.[ToDate] AS [MemberEligibilityPeriodEndDate]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId]) AS [Order]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId] DESC) AS [ReverseOrder]

     FROM

          [MemberEligibility] AS M

)

, [MemberEligibility] AS

(

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , DATEDIFF(DAY, MEH.[MemberEligibilityPeriodEndDate], MEH.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , 0 AS [GapDays]

     FROM

          [MemberEligibilityHistory] AS MEH

     WHERE

          MEH.[Order] = 1

     UNION ALL

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , ME.[MemberEligibilityStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , ME.[CoveredDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodEndDate], ME.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , ME.[GapDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodStartDate], MEH.[MemberEligibilityPeriodEndDate]) AS [GapDays]

     FROM

          [MemberEligibility] AS ME

     INNER JOIN

          [MemberEligibilityHistory] AS MEH ON

               ME.[MemberId] = MEH.[MemberId]

               AND ME.[Order] + 1 = MEH.[Order]

)

 

SELECT

     ME.[MemberId]

     , ME.[Member]

     , ME.[MemberEligibilityStartDate]

     , ME.[MemberEligibilityEndDate]

     , ME.[CoveredDays]

     , ME.[GapDays]

FROM

     [MemberEligibility] AS ME

WHERE

     ME.[ReverseOrder] = 1

ORDERBY

     ME.[Member];

, , , , , , , , , , ,

Leave a comment

%d bloggers like this: