please help me with sql query (fine tune the sql query)

[poornas]

please go through the below query. SELECT DR.id , case PC.id when 4 then sum(amount) end , case PC.id when 5 then sum(amount) end , case PC.id when 3 then sum(amount) end , case PC.id when 6 then sum(amount) end , case when pc.id = 2 and DP.id = 211 then sum(amount) end , case when pc.id = 2 and DP.id = 210 then sum(amount) end , case when pc.id = 2 and DP.id = 212 then sum(amount) end , case when pc.id = 2 and DP.id = 213 then sum(amount) end , case when pc.id = 2 and DP.id = 214 then sum(amount) end , case when pc.id = 2 and DP.id = 197 then sum(amount) end , case when PC.id = 1 and DP.id3 = 45 then sum(amount) end FROM table1 DR LEFT JOIN table2 DP ON DP.id1 = DR.id1 LEFT JOIN table3 PL ON DP.id2 = PL.id LEFT JOIN table4 PC ON PL.id1 = PC.id WHERE DR.date = GETDATE() group by DR.id, PC.id, DP.id, DP.id3 hope you understood my requirement. i didn't think this is the right way to do. please help any one can do this in a proper manner. thanks in advance.

[PSK_]

poornas wrote:

hope you understood my requirement.

What is your requirement??

Regards, Prakash Kalakoti

[i j russell]

SELECT DR.id,
SUM(case PC.id when 4 then amount else 0 end) AS PCID4,
SUM(case PC.id when 5 then amount else 0 end) AS PCID5,
SUM(case PC.id when 3 then amount else 0 end) AS PCID3,
SUM(case PC.id when 6 then amount else 0 end) AS PCID6,
SUM(case when pc.id = 2 and DP.id = 211 then amount else 0 end) AS DPID211,
SUM(case when pc.id = 2 and DP.id = 210 then amount else 0 end) AS DPID210,
SUM(case when pc.id = 2 and DP.id = 212 then amount else 0 end) AS DPID212,
SUM(case when pc.id = 2 and DP.id = 213 then amount else 0 end) AS DPID213,
SUM(case when pc.id = 2 and DP.id = 214 then amount else 0 end) AS DPID214,
SUM(case when pc.id = 2 and DP.id = 197 then amount else 0 end) AS DPID197,
SUM(case when PC.id = 1 and DP.id3 = 45 then amount else 0 end) AS DPID45
FROM
table1 DR
LEFT JOIN table2 DP
ON DP.id1 = DR.id1
LEFT JOIN table3 PL
ON DP.id2 = PL.id
LEFT JOIN table4 PC
ON PL.id1 = PC.id
WHERE
DR.date = GETDATE()
group by
DR.id

modified on Tuesday, March 23, 2010 11:24 AM

[Corporal Agarn]

I had thought about answering this as I knew something was wrong but could not think what was. i.j.russell you have hit it on the head.