Solved

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

Posted on 2016-07-20
8
9 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now