Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with TSQL Query Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 5:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:50 PM
Points: 377, Visits: 2,265
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
10 1 1
10 2 2
10 3 3
10 4 4




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
Post #1483510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse