Solved

I have a query which is taking more than 2 hours to run, can anyone solve this?

Posted on 2014-03-17
9
335 Views
Last Modified: 2014-03-21
/* This is the particular code, which is basically a data of column name [Updated bucket], it has lot of sun queries in it, and it is taking more than 3 hours to run because the particular table PACT.dbo.SQS_EDW_Source z has 6 million records in it. Little confused how to get the performance improve. Please help me out.

,
CASE
            --Is nonpar waiver
            WHEN z.SRC_PAR_CD IN ('E','O','S','W')
            THEN 'Nonpar Waiver'
  
--      --**Amendment Mailed**
        WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT b.PROV_TIN
            FROM PACT.dbo.SQS_Mailed_TINs_010614 b
                  )
            THEN 
                  (SELECT DISTINCT b.Mailing 
                  FROM PACT.dbo.SQS_Mailed_TINs_010614 b
                  WHERE z.PROV_TAX_ID = b.PROV_TIN
                  )

            --Is Puerto Rico of Lifesynch
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT a.PROV_TAX_ID 
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.Bucket <> 'Nonpar'
                  )
            THEN 
                  (SELECT DISTINCT a.Bucket
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)

            --**Top Objecting Systems**
            WHEN z.SYSTEMNAME IN 
                  ('ADVENTIST HEALTH SYSTEM','ASCENSION HEALTH ALLIANCE','AULTMAN HEALTH FOUNDATION','BANNER HEALTH SYSTEM','BERT FISH MEDICAL CENTER','BETHESDA MEMORIAL HOSPITAL')
            THEN 'Top Objecting Systems' 

            --**Other Objecting Hospitals**
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        PACT.dbo.SQS_EDW_Source z
                  WHERE 
                        z.PROV_TAX_ID IN 
                              (SELECT DISTINCT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj
                                                WHERE obj.[Objector?] = 'Yes' 
                                                AND obj.[TINs Provided By:] <> 'Unknown'
                                                --PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              AND z.SEQUEST_AMT < 31000
                              AND z.SYSTEMNAME <> 'NULL'
                  )
            THEN 'Other Objecting Hospitals'
       
  --      --**Objecting Physicians**
        WHEN 
                  z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              z.PROV_TAX_ID
                        FROM 
                              PACT.dbo.SQS_EDW_Source z
                        WHERE 
                              z.PROV_TAX_ID IN 
                                    (SELECT DISTINCT obj.TIN 
                                    FROM PACT.dbo.SQS_Provider_Tracking obj --PACT.dbo.SQS_Objectors_01202014b
                                    WHERE obj.[Objector?] = 'Yes'
                                    AND obj.[TINs Provided by:] <> 'Unknown'
                                    )
                        AND z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI')
                        )
            THEN 'Objecting Physicians'   
            
            
--**********ALL OBJECTORS SHOULD HAVE BEEN BUCKETED AT THIS POINT IN THE QUERY**********


--      --**Non-Objecting Hospitals**
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        z.PROV_TAX_ID
                  FROM 
                        PACT.dbo.SQS_EDW_Source z
                  WHERE 
                        z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                        --AND z.SEQUEST_AMT < 31000
                        AND z.SYSTEMNAME <> 'NULL'
                  )
            THEN 'Non-Objecting Hospitals'            
        
  --      --**Under Review with CoC**
        --WHEN z.PROV_TAX_ID IN
        --          (SELECT 
        --                            a.PROV_TAX_ID
        --                      FROM 
        --                            PACT.dbo.[SQS_CoC_Potential_Mail_List] a 
        --                      --      LEFT JOIN PACT.dbo.SQS_Bucketed_Details_SMG b 
        --                      --      on a.PROV_TAX_ID = b.PROV_TAX_ID 
        --                      --      where b.actual_sequestered_amt IS not null
        --                      --GROUP BY 
        --                      --      a.PROV_TAX_ID 
        --                      --having SUM(b.Actual_Sequestered_Amt) <> 0
        --                      ) 
        --    THEN 'Under Review with CoC' 

        --    ----**Outstanding Contracts for Review**
        --when SUM(z.sequest_amt)<1000 then 'Less Than $1k Potential Exposure'
        --when sum(z.sequest_amt)<2000 then '1k-2k Potential Exposure'
        --when sum(z.sequest_amt)<3000 then '2k-3k Potential Exposure'
        --when sum(z.sequest_amt)<4000 then '3k-4k Potential Exposure'
        --when sum(z.sequest_amt)<5000 then '4k-5k Potential Exposure'
        --when sum(z.sequest_amt)<6000 then '5k-6k Potential Exposure'
        --when sum(z.sequest_amt)<7000 then '6k-7k Potential Exposure'
        --when sum(z.sequest_amt)<8000 then '7k-8k Potential Exposure'
        --when sum(z.sequest_amt)<9000 then '8k-9k Potential Exposure'
        --when sum(z.sequest_amt)<10000 then '9k-10k Potential Exposure'
        --when sum(z.sequest_amt)<20000 then '10k-20k Potential Exposure'
        --when sum(z.sequest_amt)<30000 then '20k-30k Potential Exposure'
        --when sum(z.sequest_amt)<40000 then '30k-40k Potential Exposure'
        --when sum(z.sequest_amt)<50000 then '40k-50k Potential Exposure'
        --else 'Greater than 50k Potential Exposure'
        WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (3rd Wave)')
		THEN 'Amendment Mailed (3rd Wave)'
		
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (4th Wave)')
		THEN 'Amendment Mailed (4th Wave)'
		
		
		 WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Non-Objecting Bilateral Physicians')
		THEN 'Non-Objecting Bilateral Physicians'
	
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'More Research Needed')
		THEN 'More Research Needed'
		
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Objector')
		THEN 'ERROR OBJECTOR NOT PICKED UP EARLIER'
		
		
        else 'Market Review/Preparing to Mail'
    
    END AS [Updated Bucket]

Open in new window

------------------------------------------------------

Thanks,
Ranjit
0
Comment
Question by:Aparanjith
  • 4
  • 3
  • 2
9 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39935465
In SSMS under the Query Menu option there is a Estimated Execution Plan and can you select that option having your query open? This should show you at least if there is/are some missing indexes that will help improve the execution time.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39935471
Also I would convert ALL the IN statements with DISTINCT into EXISTS statements without the Distinct.
0
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 39935475
Hard to say without seeing the makeup of the table, index etc

I'd say if the fields you are using for selection in the large table are not indexed then add indexes for them.  From what I can see you need to look at the fields such as

PROV_TAX_ID
LCLM_RSTMT_TREND_CAT_CD
etc

Wayne
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Aparanjith
ID: 39935485
I did but as the column, can contain null values, its not showing up for indexes, it is all about the table PACT.dbo.SQS_EDW_Source z which has 6 million records in it. And it has been used for 3 or 4 times in the query. I do not have idea, how to do it, can you help me out
0
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 39935490
You can add indexes for fields that allow Null, you just can't set unique keys.

What indexes are on that big table?

Wayne
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39935512
Try this one and compare the result sets to be identical:

SELECT
CASE
            --Is nonpar waiver
            WHEN z.SRC_PAR_CD IN ('E','O','S','W')
            THEN 'Nonpar Waiver'
 
--      --**Amendment Mailed**
        WHEN EXISTS
                  (SELECT b.PROV_TIN
                              FROM PACT.dbo.SQS_Mailed_TINs_010614 b WITH (NOLOCK) WHERE z.PROV_TAX_ID = b.PROV_TIN)
            THEN
                  (SELECT DISTINCT b.Mailing
                  FROM PACT.dbo.SQS_Mailed_TINs_010614 b WITH (NOLOCK)
                  WHERE z.PROV_TAX_ID = b.PROV_TIN
                  )

            --Is Puerto Rico of Lifesynch
            WHEN EXISTS
                  (SELECT a.PROV_TAX_ID
                        FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a WITH (NOLOCK)
                          WHERE a.Bucket <> 'Nonpar'
                                          AND a.PROV_TAX_ID=z.PROV_TAX_ID
                  )
            THEN
                  (SELECT DISTINCT a.Bucket
                  FROM PACT.dbo.SQS_NonPar_PR_LS_TINs a WITH (NOLOCK)
                  WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)

            --**Top Objecting Systems**
            WHEN z.SYSTEMNAME IN
                  ('ADVENTIST HEALTH SYSTEM','ASCENSION HEALTH ALLIANCE','AULTMAN HEALTH FOUNDATION','BANNER HEALTH SYSTEM','BERT FISH MEDICAL CENTER','BETHESDA MEMORIAL HOSPITAL')
            THEN 'Top Objecting Systems'

            --**Other Objecting Hospitals**
            WHEN EXISTS
                  (SELECT z.PROV_TAX_ID
                  FROM PACT.dbo.SQS_EDW_Source z  WITH (NOLOCK)
                  WHERE EXISTS
                             (SELECT obj.TIN FROM PACT.dbo.SQS_Provider_Tracking obj WITH (NOLOCK)
                                                WHERE obj.[Objector?] = 'Yes'
                                                AND obj.[TINs Provided By:] <> 'Unknown'
                                                AND obj.TIN = z.PROV_TAX_ID
                                                --PACT.dbo.SQS_Objectors_01202014b
                              )
                              AND z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                              AND z.SEQUEST_AMT < 31000
                              AND z.SYSTEMNAME <> 'NULL'
                  )
            THEN 'Other Objecting Hospitals'
       
  --      --**Objecting Physicians**
        WHEN EXISTS  
                        (SELECT z.PROV_TAX_ID
                        FROM PACT.dbo.SQS_EDW_Source z WITH (NOLOCK)
                        WHERE EXISTS
                                    (SELECT DISTINCT obj.TIN
                                    FROM PACT.dbo.SQS_Provider_Tracking obj WITH (NOLOCK) --PACT.dbo.SQS_Objectors_01202014b
                                    WHERE obj.[Objector?] = 'Yes'
                                    AND obj.[TINs Provided by:] <> 'Unknown'
                                    AND obj.TIN = z.PROV_TAX_ID
                                    )
                        AND z.LCLM_RSTMT_TREND_CAT_CD NOT IN ('HO','HI')
                        )
            THEN 'Objecting Physicians'  
           
           
--**********ALL OBJECTORS SHOULD HAVE BEEN BUCKETED AT THIS POINT IN THE QUERY**********


--      --**Non-Objecting Hospitals**
            WHEN EXISTS
                  (SELECT z.PROV_TAX_ID
                  FROM PACT.dbo.SQS_EDW_Source z WITH (NOLOCK)
                  WHERE z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')
                        --AND z.SEQUEST_AMT < 31000
                        AND z.SYSTEMNAME <> 'NULL'
                  )
            THEN 'Non-Objecting Hospitals'            
       
  --      --**Under Review with CoC**
        --WHEN z.PROV_TAX_ID IN
        --          (SELECT
        --                            a.PROV_TAX_ID
        --                      FROM
        --                            PACT.dbo.[SQS_CoC_Potential_Mail_List] a
        --                      --      LEFT JOIN PACT.dbo.SQS_Bucketed_Details_SMG b
        --                      --      on a.PROV_TAX_ID = b.PROV_TAX_ID
        --                      --      where b.actual_sequestered_amt IS not null
        --                      --GROUP BY
        --                      --      a.PROV_TAX_ID
        --                      --having SUM(b.Actual_Sequestered_Amt) <> 0
        --                      )
        --    THEN 'Under Review with CoC'

        --    ----**Outstanding Contracts for Review**
        --when SUM(z.sequest_amt)<1000 then 'Less Than $1k Potential Exposure'
        --when sum(z.sequest_amt)<2000 then '1k-2k Potential Exposure'
        --when sum(z.sequest_amt)<3000 then '2k-3k Potential Exposure'
        --when sum(z.sequest_amt)<4000 then '3k-4k Potential Exposure'
        --when sum(z.sequest_amt)<5000 then '4k-5k Potential Exposure'
        --when sum(z.sequest_amt)<6000 then '5k-6k Potential Exposure'
        --when sum(z.sequest_amt)<7000 then '6k-7k Potential Exposure'
        --when sum(z.sequest_amt)<8000 then '7k-8k Potential Exposure'
        --when sum(z.sequest_amt)<9000 then '8k-9k Potential Exposure'
        --when sum(z.sequest_amt)<10000 then '9k-10k Potential Exposure'
        --when sum(z.sequest_amt)<20000 then '10k-20k Potential Exposure'
        --when sum(z.sequest_amt)<30000 then '20k-30k Potential Exposure'
        --when sum(z.sequest_amt)<40000 then '30k-40k Potential Exposure'
        --when sum(z.sequest_amt)<50000 then '40k-50k Potential Exposure'
        --else 'Greater than 50k Potential Exposure'
        WHEN EXISTS
                  (SELECT      qz.PROV_TIN
                  FROM
                        [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz WITH (NOLOCK)
                        where qz.Mailing = 'Amendment Mailed (3rd Wave)'
                                    AND qz.PROV_TIN = z.PROV_TAX_ID )
            THEN 'Amendment Mailed (3rd Wave)'
            
            
            WHEN EXISTS
                  (SELECT qz.PROV_TIN
                  FROM
                        [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz WITH (NOLOCK)
                        where qz.Mailing = 'Amendment Mailed (4th Wave)'
                                    AND qz.PROV_TIN = z.PROV_TAX_ID)
            THEN 'Amendment Mailed (4th Wave)'
            
            
             WHEN EXISTS
                  (SELECT qz.PROV_TIN
                  FROM
                        [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz WITH (NOLOCK)
                        where qz.Mailing = 'Non-Objecting Bilateral Physicians'
                                    AND qz.PROV_TIN = z.PROV_TAX_ID)
            THEN 'Non-Objecting Bilateral Physicians'
      
            
            WHEN EXISTS
                  (SELECT qz.PROV_TIN
                  FROM
                        [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz WITH (NOLOCK)
                        where qz.Mailing = 'More Research Needed'
                        AND qz.PROV_TIN = z.PROV_TAX_ID)
            THEN 'More Research Needed'
            
            
            WHEN EXISTS
                  (SELECT qz.PROV_TIN
                  FROM
                        [PACT].[HUMAD\ARS3766].[SQS_Mailed_TINs] qz WITH (NOLOCK)
                        where qz.Mailing = 'Objector' AND qz.PROV_TIN = z.PROV_TAX_ID )
            THEN 'ERROR OBJECTOR NOT PICKED UP EARLIER'
            
            
        else 'Market Review/Preparing to Mail'
   
    END AS [Updated Bucket] OPTION(MAXDOP 1);
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39935522
Check to see if there is an index on
PACT.dbo.SQS_EDW_Source

  z.LCLM_RSTMT_TREND_CAT_CD IN ('HO','HI')  AND z.SYSTEMNAME columns.

Also in the two other places where this table is used I would put the SELECT from the other smaller table first then JOIN with PACT.dbo.SQS_EDW_Source on the z.PROV_TAX_ID column.

Also make sure an index exists on z.PROV_TAX_ID column.
0
 

Author Closing Comment

by:Aparanjith
ID: 39935570
Thanks for the query
0
 

Author Comment

by:Aparanjith
ID: 39946661
Hey guys I am having problem only with this part of  the code, where we have all cases and when tried using WHEN EXISTS it only selected few cases and ignored rest of them. Can someone help me in rewriting this part of code in a different way, which can improve the performance and also selects all the cases to the [Updated bucket] column, Need help....
, CASE 
            WHEN z.SRC_PAR_CD IN ('E','O','S','W')
            THEN 'Nonpar Waiver'
 
 
  --            --Is Puerto Rico of Lifesynch
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT a.PROV_TAX_ID 
                  FROM .dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.Bucket <> 'Nonpar' 
                  )
            THEN 
                  (SELECT DISTINCT a.Bucket
                  FROM .dbo.SQS_NonPar_PR_LS_TINs a
                  WHERE a.PROV_TAX_ID = z.PROV_TAX_ID)


      --**Amendment Mailed**
        WHEN z.PROV_TAX_ID IN
                 (SELECT DISTINCT b.PROV_TIN
            FROM .dbo.SQS_Mailed_TINs_010614 b WITH (NOLOCK )
                 where not exists (select * from dbo.sqs_objector_TINs t where b.PROV_TIN = t.prov_tin))
                 and z.Hosp_Ind = 'P'
            THEN 
                  (SELECT DISTINCT b.Mailing 
                  FROM .dbo.SQS_Mailed_TINs_010614 b
                  WHERE z.PROV_TAX_ID = b.PROV_TIN
                  )
       
                  
--		--**Amendment Mailed Wave 3-5**
		WHEN z.PROV_TAX_ID In
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (3rd Wave)'
				and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
				and z.Hosp_Ind = 'P'
		THEN 'Amendment Mailed (3rd Wave)'
		
		
		 WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (4th Wave)'
				and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
				and z.Hosp_Ind = 'P'
		THEN 'Amendment Mailed (4th Wave)' 
		
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Amendment Mailed (5th Wave)'
				and not exists (select * from dbo.sqs_objector_TINs t where qz.PROV_TIN = t.prov_tin))
				and z.Hosp_Ind = 'P'
		THEN 'Amendment Mailed (5th Wave)' 
		
         
--            --**Top Objecting Systems**
            WHEN z.SYSTEMNAME IN 
                  ('ADVENTIST HEALTH SYSTEM','ASCENSION HEALTH ALLIANCE','AULTMAN HEALTH FOUNDATION')
            THEN 'Top Objecting Systems' 
            
            WHEN z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              h.PROV_TAX_ID
                        FROM 
                              #HIHO_Records h
                        INNER JOIN .dbo.SQS_Provider_Tracking obj
                        ON h.PROV_TAX_ID = obj.TIN 
                        AND obj.[Objector?] = 'Top Objector' 
                        WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
                        OR h.SMG_ID IS NOT NULL
                        )and z.Hosp_Ind = 'H'
            THEN 'Top Objecting Systems'
            
            
--            --**Other Objecting Hospitals**
            WHEN (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              h.PROV_TAX_ID
                        FROM 
                              #HIHO_Records h
                        INNER JOIN .dbo.SQS_Provider_Tracking obj
                        ON h.PROV_TAX_ID = obj.TIN 
                        AND obj.[Objector?] = 'Objector' 
                        WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
                        OR h.SMG_ID IS NOT NULL
                        )and z.Hosp_Ind = 'H')
            THEN 'Other Objecting Hospitals'
       
  --      --**Objecting Physicians**

              WHEN (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              obj.TIN
                        FROM .dbo.SQS_Provider_Tracking obj
                        
                        WHERE obj.[Objector?] in ('Objector','Top Objector')
                        and z.PROV_TAX_ID = obj.TIN
                       )
                       and z.Hosp_Ind = 'P')
            THEN 'Objecting Physicians'   
            
            
            
         --****Rejecting Hospitals****
                                       
            WHEN (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              h.PROV_TAX_ID
                        FROM 
                              #HIHO_Records h
                        INNER JOIN .dbo.SQS_Provider_Tracking obj
                        ON h.PROV_TAX_ID = obj.TIN 
                        AND obj.[Objector?] = 'Rejector'
                        WHERE z.PROV_TAX_ID = h.PROV_TAX_ID
                        OR h.SMG_ID IS NOT NULL
                        )and z.Hosp_Ind = 'H')
            THEN 'Rejecting Hospitals'
            
            
          --****Rejecting Physciains****
            WHEN 
                (z.PROV_TAX_ID IN 
                        (SELECT DISTINCT 
                              obj.TIN
                        FROM .dbo.SQS_Provider_Tracking obj
                        
                        WHERE z.PROV_TAX_ID = obj.TIN
                        AND obj.[Objector?] = 'Rejector')
                        and z.Hosp_Ind = 'P')
            THEN 'REjecting Physicians'   
          
----**********ALL OBJECTORS SHOULD HAVE BEEN BUCKETED AT THIS POINT IN THE QUERY**********


--      --**Non-Objecting Hospitals**
            WHEN z.PROV_TAX_ID IN
                  (SELECT DISTINCT 
                        h.PROV_TAX_ID
                  FROM 
                        #HIHO_Records h
                  WHERE 
                        (z.PROV_TAX_ID = h.PROV_TAX_ID) 
                       OR h.SMG_ID IS NOT NULL)
                  and z.Hosp_Ind = 'H'
            THEN 'Non-Objecting Hospitals'            
 
   --		**Outstanding Contracts for Review**
		 WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[SQS_Mailed_TINs] qz
				where qz.Mailing = 'Non-Objecting Bilateral Physicians'
				AND z.PROV_TAX_ID = qz.PROV_TIN)
		Then 'Non-Objecting Bilateral Physicians'
		
			When  z.prov_tax_id in 
			(select distinct 
				p.prov_tax_id
				from  dbo.SQS_CoC_Potential_Mail_List p
				where p.amendmentrights <> 'Unilateral'
				AND z.prov_tax_id = p.prov_tax_id)
		THEN 'Non-Objecting Bilateral Physicians'
		
		WHEN z.PROV_TAX_ID IN
			(SELECT DISTINCT
				qz.PROV_TIN
			FROM
				[SQS_Mailed_TINs] qz
				where qz.Mailing = 'More Research Needed'
				AND qz.PROV_TIN = z.PROV_TAX_ID)
		THEN 'More Research Needed'
		
		WHEN z.PROV_TAX_ID IN (SELECT DISTINCT qz.PROV_TIN FROM [SQS_Mailed_TINs] qz where qz.Mailing = 'Objector' AND qz.PROV_TIN = z.PROV_TAX_ID)
		THEN 'ERROR'
		
       else 'Market Review/Preparing to Mail'
    
    END AS [Updated Bucket]

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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