Solved

How to Write the results from the CTE Recursive to a table

Posted on 2016-07-20
8
14 Views
Last Modified: 2016-09-30
I am running the following code that was provided to me earlier from a previous question,  now I want to take the results of this and write it to a table.  It is running but I do not see any rows in the new table,  will it write them all at the end?

Create table dbo.BlueMCAid_Test

( MBR_MBR varchar(30)not null,
  EffectiveDate varchar(10)      not null,
  TerminationDate varchar(10) not null,
  PCP_PROV_NBR varchar(27) null
  )
go


;WITH    AllDates
          -- getting a list of all dates we have to work with for creating the ranges, just all the start and end dates from both tables
          AS ( SELECT   ( CAST(TEST1.PCP_EFCTV_DT AS DATE) ) [Day]
               FROM     dbo.Blue_Raw_MCaid_Member_Enrollment_PCP TEST1
               UNION
               SELECT   ( CAST(TEST1.PCP_TRMNTN_DT AS DATE) ) Start
               FROM     dbo.Blue_Raw_MCaid_Member_Enrollment_PCP TEST1
               UNION
               SELECT   ( CAST(TEST2.ENR_START AS DATE) ) Start
               FROM     dbo.Blue_Raw_MCaid_Member_Enrollment TEST2
               UNION
               SELECT   ( CAST(TEST2.ENR_END AS DATE) ) Start
               FROM     dbo.Blue_Raw_MCaid_Member_Enrollment TEST2
             ),
        EndDay
          -- finding the last day in our range but limiting it with CURRENT_TIMESTAMP to keep from going to end of time
          AS ( SELECT   MAX(AllDates.Day) Last
               FROM     AllDates
               WHERE    AllDates.Day = '12/31/2016'
             ),
        DaysPart1
          -- generating one day record for every day in our range, this one excludes the outliers, 8888-12-31
          AS ( SELECT   MIN(AllDates.Day) Day
               FROM     AllDates
               UNION ALL
               SELECT   DATEADD(DAY, 1, DaysPart1.Day)
               FROM     DaysPart1
               WHERE    DaysPart1.Day < ( SELECT EndDay.Last FROM EndDay
                                        ) -- Could not use max here so had to have the EndDay cte
             ),
        Days
          -- adding back in just the outlier dates to the range
          AS ( SELECT   DaysPart1.Day
               FROM     DaysPart1
               UNION
               SELECT   AllDates.Day
               FROM     AllDates
               WHERE    AllDates.Day > '12/31/2016'
             )
    SELECT  RangeGrouping.MBR_NBR, RangeGrouping.MinDay AS EffectiveDate, RangeGrouping.MaxDay AS TerminationDate, RangeGrouping.PCP_PROV_NBR -- The End Results
    FROM    ( SELECT    T2.MBR_NBR, MIN(T2.Day) MinDay, MAX(T2.Day) MaxDay, T1.PCP_PROV_NBR, T2.ENR_START, T2.ENR_END, T1.PCP_EFCTV_DT, T1.PCP_TRMNTN_DT  -- Data we will have for each range of dates, the Min and Max Day will be the Effective and Termination dates
              FROM      ( SELECT    Days.Day ,
                                    TI.MBR_NBR ,
                                    TI.ENR_START ,
                                    TI.ENR_END
                          FROM      Days
                          INNER JOIN dbo.Blue_Raw_MCaid_Member_Enrollment TI ON Days.Day BETWEEN TI.ENR_START AND TI.ENR_END
                        ) T2 -- Joins the enrollment table to our Days to add the Day field for the final left outer join
              LEFT OUTER JOIN ( SELECT  Days.Day ,
                                        TI.MBR_NBR ,
                                        TI.PCP_PROV_NBR ,
                                        TI.PCP_EFCTV_DT ,
                                        TI.PCP_TRMNTN_DT
                                FROM    Days
                                INNER JOIN dbo.Blue_Raw_MCaid_Member_Enrollment_PCP TI ON Days.Day BETWEEN TI.PCP_EFCTV_DT AND TI.PCP_TRMNTN_DT
                              ) T1 -- Joins the Provider table to our Days to add the Day field for the final left outer join
                        ON T1.MBR_NBR = T2.MBR_NBR
                           AND T2.Day = T1.Day  -- Now we can join between Member and Day to get complete list of all ranges
              GROUP BY  T2.MBR_NBR, T1.PCP_PROV_NBR, T2.ENR_START, T2.ENR_END, T1.PCP_EFCTV_DT, T1.PCP_TRMNTN_DT  -- Grouping to get back to one record per range
            ) RangeGrouping
    ORDER BY RangeGrouping.MBR_NBR, EffectiveDate -- I just added this for easier visual on results
   
    Insert into dbo.BlueMCAid_Test (MBR_MBR,EffectiveDate,TerminationDate,PCP_PROV_NBR)
     ( SELECT  MBR_NBR, MinDay AS EffectiveDate, MaxDay AS TerminationDate, PCP_PROV_NBR from RangeGrouping)
     
    OPTION  ( MAXRECURSION 0 ); -- have to set this or the DaysPart1 CTE will fail when passing the default 100 recursions mark
0
Comment
Question by:knamc99
  • 4
  • 3
8 Comments
 

Author Comment

by:knamc99
ID: 41721260
I need to be able to run this in SSIS and take the results and Transform to the final table.
0
 

Author Comment

by:knamc99
ID: 41721265
Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I just received this message and thought with the value 0 it would override the default of 100?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41721271
To INSERT (assuming that's what you mean by a table) all you need is the INSERT clause between the end of the last CTE and the SELECT.

blah blah blah
   Days
          -- adding back in just the outlier dates to the range
          AS ( SELECT   DaysPart1.Day
               FROM     DaysPart1
               UNION
               SELECT   AllDates.Day
               FROM     AllDates
               WHERE    AllDates.Day > '12/31/2016'
             )
-- Looky here
INSERT INTO SomeTable (MBR_NBR, EffetiveDate, TerminationDate, PCP_PROV_NBR)

    SELECT  RangeGrouping.MBR_NBR, RangeGrouping.MinDay AS EffectiveDate, RangeGrouping.MaxDay AS TerminationDate, RangeGrouping.PCP_PROV_NBR -- The End Results
blah blah blah
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 41721277
>I need to be able to run this in SSIS and take the results and Transform to the final table.
Can't do both, write (insert) into a table, and also keep that set in an SSIS data flow for subsequent transformations.  A cleaner workaround would be to write a SQL Stored Procedure that has this statement plus whatever transformation logic, then have it do the insert, then in SSIS all you need to do is execute the SP.
0
 

Author Comment

by:knamc99
ID: 41721279
Now I am running into the problem of max recusion,  these tables have millions of rows
0
 

Author Comment

by:knamc99
ID: 41721293
If I can write all the data to a table,  then I can use that table as my source in SSIS.  But now I first need to get around the MAXRECURSION limitation.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 41721322
>If I can write all the data to a table,  then I can use that table as my source in SSIS.
Correct, but unless there is new functionality with the higher versions of SSIS that will be a two-step process, a SP or data flow to write the rows, and another data flow to select again and do something with them.

>But now I first need to get around the MAXRECURSION limitation.
Not my strength, so I'll back out of this question gracefully to encourage other experts to respond.  If you don't get any answers then it would be a good idea to close this question for the SSIS answer, and create a new question to focus on the maxrecursion.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question