troubleshooting Question

Date Ranges/Case Statement

Avatar of knamc99
knamc99 asked on
Microsoft SQL Server 2008SQL
14 Comments1 Solution145 ViewsLast Modified:
I had posted this before and the solution I got back worked great (CTE's and Recursion) if I only ran it for a member at a time. With dates going back to 1994 and out as far as 2199 (which I adjusted using a case statement) it still was creating 16 billion rows for the date ranges which is not feasible for our databases.  I need to find a solution using a Case statement or another process that doesn't chew up all the memory or the tempdb.

Again here is my Current case statement and what I am getting as results, along with the sample data:

SELECT ENROLL.MBR_NBR,
            CASE
        WHEN PCP.PCP_EFCTV_DT < ENROLL.ENR_START THEN ENROLL.ENR_START
        WHEN PCP.PCP_EFCTV_DT IS NULL THEN ENROLL.ENR_START
        WHEN PCP.PCP_EFCTV_DT > ENROLL.ENR_END THEN ENROLL.ENR_START
        ELSE PCP.PCP_EFCTV_DT
    END AS 'EffectiveDate',                                                                                
    CASE
        WHEN PCP.PCP_TRMNTN_DT > ENROLL.ENR_END THEN ENROLL.ENR_END
        WHEN PCP.PCP_TRMNTN_DT IS NULL THEN ENROLL.ENR_END
        WHEN PCP.PCP_TRMNTN_DT < ENROLL.ENR_START THEN ENROLL.ENR_END
        ELSE PCP.PCP_TRMNTN_DT
    END AS 'TerminationDate',
      CASE
         WHEN PCP.[PCP_PROV_NBR] = 'NOPCP' then ''
       ELSE PCP.[PCP_PROV_NBR]
       END as PCP_PROV_NBR
                              
                                                                                                                                                  
FROM dbo.Blue_Raw_MCaid_Member_Enrollment  ENROLL left join dbo.Blue_Raw_MCaid_Member_Enrollment_PCP  PCP ON ENROLL.MBR_NBR=PCP.MBR_NBR
                                          AND (PCP.PCP_EFCTV_DT  BETWEEN ENROLL.ENR_START
                                          AND (CASE WHEN  ENROLL.ENR_END = '8888-12-31'
                                                        THEN '9999-12-31'  
                                                        ELSE ENROLL.ENR_END  
                                                        END)
                                          OR PCP.PCP_TRMNTN_DT  BETWEEN ENROLL.ENR_START
                                          AND (CASE WHEN ENROLL.ENR_END  = '8888-12-31'
                                                        THEN '9999-12-31'
                                                        ELSE ENROLL.ENR_END  
                                                        END)
                                          OR (PCP.PCP_EFCTV_DT  BETWEEN ENROLL.ENR_END
                                          AND (CASE WHEN ENROLL.ENR_END  = '8888-12-31'
                                                        THEN '9999-12-31'
                                                        ELSE ENROLL.ENR_END  
                                                        END)
                                          OR (PCP.PCP_EFCTV_DT  < ENROLL.ENR_START  and PCP.PCP_TRMNTN_DT  > ENROLL.ENR_END)                                          
                                                                        OR PCP.PCP_EFCTV_DT  IS NULL))



Result Set:  Unique Members is 2.7 million .  I need to be able to load this data to a table in the database which I can do using SSIS.

RESULT SET FROM QUERY                        
MBR_NBR      EffectiveDate         TerminationDate      PCP_PROV_NBR         Outcome
123456              1/1/2012               9/30/2013                       NULL                     Good Row
123456              11/1/2013             1/31/2014                       NULL                     This row is missing
123456               2/1/2014               9/30/2015                       ABCD                     Good Row
123456              10/1/2015             10/31/2015                     ABCD                     Good Row
123456              11/1/2015              12/31/8888                    WXYZ                     Good Row

Here is the test Data


Blue_Raw_MCaid_Member_Enrollment  (This table has 7.5 million rows)

MEM_NBR      ENR_START      ENR_END      PCP_PROV_NBR
123456               1/1/2012      9/30/2013      UNKNOWN
123456               11/1/2013      9/30/2015      UNKNOWN
123456               10/1/2015      12/31/8888      UNKNOWN

Blue_Raw_MCaid_Member_Enrollment_PCP   (This table has 5.8 million rows)


MBR_NBR      PCP_PROV_NBR      PCP_EFCTV_DT      PCP_TRMNTN_DT
123456                      ABCD               2/1/2014              10/31/2015
123456                      WXYZ             11/1/2015              12/31/8888
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros