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];

Advertisements

, , , , , , , , , , ,

  1. Leave a comment

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: