August 12, 2013 at 5:38 pm
I have 3 diff tables and based upon that I need to rank the Cid..
Req: The table one should check if the Cno and Cid exists in Tbl2 if exists then get that rank if it doesn exist then check Tbl3 then get tat rank else rank the based on CID desc
I have tried to write a query but it didn't work out so can tou please help me write the query
So the o/p required is
1011
1022
1033
1044
CREATE TABLE #temp1
( CNo INT NOT NULL,
Cid INT NOT NULL
)
CREATE TABLE #temp2
( CNo INT NOT NULL,
Cid INT NOT NULL,
RNK INT
)
CREATE TABLE #temp3
( CNo INT NOT NULL,
Cid INT NOT NULL,
RNK INT
)
INSERT INTO #temp1
SELECT 10,4
UNION
SELECT 10,2
UNION
SELECT 10,3
UNION
SELECT 10,1
INSERT INTO #temp2
SELECT 10,1,1
UNION
SELECT 10,2,2
INSERT INTO #temp3
SELECT 10,3,1
UNION
SELECT 10,2,2
UNION
SELECT 10,4,3
SELECT * FROM #temp1
SELECT * FROM #temp2
SELECT * FROM #temp3
--DELETE FROM #temp3 WHERE Cid = 3
SELECT T1.CNo,T1.Cid,
CASE WHEN T2.Cid IS NOT NULL THEN T2.RNK
WHEN T2.Cid IS NULL AND T3.Cid IS NOT NULL THEN CASE WHEN Mx.MX > 0 THEN Mx.MX + T3.RNK ELSE T3.RNK END END
-- WHEN T2.CNo is NULL AND T3.CNo is NULL THEN (Mx.MX + 1) + 1 END--WHEN T3.customerid IS NOT NULL THEN CASE WHEN Mx.MX > 0 THEN Mx.MX + 1 ELSE T3.RNK END ) END
FROM #temp1 T1
LEFT JOIN #temp2 T2 ON T1.Cno = T2.Cno AND T1.Cid = T2.Cid
LEFT JOIN #temp3 T3 ON T1.Cno = T3.Cno AND T1.Cid = T3.Cid
LEFT JOIN (SELECT Cno,MAX(rnk) AS mx FROM #temp2 GROUP BY Cno) MX ON T1.Cno = Mx.CNo
LEFT JOIN (SELECT CNo,MAX(rnk) AS mx1 FROM #temp2 GROUP BY CNo) MX1 ON T1.CNo = Mx1.CNo
WHERE t1.CNo = 10
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply