Help with TSQL Query

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  August 12, 2013 at 5:38 pm

  #395288

  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