1

Possible Duplicate:
Query with many CASE statements - optimization

Hi guys,

I have one very dirty query that per sure can be optimized because there are so many CASE statements in it!

SELECT (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 

    WHEN 2 THEN fw.fw_id
 WHEN 3 THEN s.sw_Id
 WHEN 4 THEN id.ia_id END) as Deal_Id,  
max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id 
              WHEN 2 THEN fw.Trans_Id
                WHEN 3 THEN s.Trans_Id
              WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax
INTO #MaxRazlicitOdNull
FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id     AND pa.KplusTable_Id=1
      LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2  
         LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3
           LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4
 WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA 
                       WHEN 2 THEN fw.BROJ_TIKETA
              WHEN 3 THEN s.tiket
              WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' 
GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 
             WHEN 2 THEN fw.fw_id
       WHEN 3 THEN s.sw_Id
       WHEN 4 THEN id.ia_id END

Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome!

TnX in advance!

Nemanja

Community
  • 1
  • 1
Nemanja Vujacic
  • 925
  • 5
  • 14
  • 25
  • 2
    duplicate of [Query with many CASE statements - optimization](http://stackoverflow.com/questions/4526628/query-with-many-case-statements-optimization) – mechanical_meat Dec 24 '10 at 14:10
  • Give us an overview about the tables and relationships, that would really help to better understand what's going on here. – Doc Brown Dec 24 '10 at 14:13

0 Answers0