SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with TSQL Query


Help with TSQL Query

Author
Message
Sri8143
Sri8143
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1191 Visits: 2469
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search