Solved

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

Posted on 2014-03-17
9
333 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

707 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

18 Experts available now in Live!

Get 1:1 Help Now