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
To me this looks like a botched attempt in sub-typing. This is what I think you have now.
Based on the model, the following should work:
;
with
q_00 as (
select
pa.Deal_Id as Deal_Id
, coalesce(sp.BROJ_TIKETA, fw.BROJ_TIKETA, sw.tiket, ia.BROJ_TIKETA, '') as Ticket_No
, coalesce(sp.Trans_Id, fw.Trans_Id, sw.Trans_Id, ia.Trans_Id) as Trans_Id
from #PotencijalniAktuelni as pa
left join kplus_sp as sp on sp.sp_Id = pa.Deal_Id and pa.KplusTable_Id = 1
left join kplus_fw as fw on fw.fw_Id = pa.Deal_Id and pa.KplusTable_Id = 2
left join dev_sw as sw on sw.sw_Id = pa.Deal_Id and pa.KplusTable_Id = 3
left join kplus_ia as ia on ia.ia_Id = pa.Deal_Id and pa.KplusTable_Id = 4
)
select
Deal_Id
, max(Trans_Id) as TransId_CurrentMax
into #MaxRazlicitOdNull
from q_00
where Ticket_No <> ''
group by Deal_Id ;
SQL Server 2005 +