Re: Need a logic to retireve data from the table ..
You need to check your requirement against expected results!
The following SQL gives the answer:
Id Dept CalcSal 200 b 20 300 c 1200 400 d 40
This is because: 1. Id 300 has departments C and D. 30 * 40 is 1200, not 120 2. Because ID 300 has C and D, we only get the combination. So your expected row 300 c 30 should not be there.
Select TopDept.Id , TopDept.Dept , CASE WHEN TopDept.Dept = 'C' AND DDept.Dept = 'D' THEN TopDept.Sal * DDept.Sal ELSE TopDept.Sal END As CalcSal From -- Get the row with the Dept prioritised (Select MD.Id , MD.Dept , MD.Sal From Tbl MD Where MD.Id Not In (Select Id From Tbl Where Dept = 'A') Qualify Row_Number () OVER (Partition By MD.Id Order By MD.Dept ASC) = 1 ) AS TopDept Left Join Tbl DDept On TopDept.Id = DDept.Id And TopDept.Dept = 'C' And DDept.Dept = 'D' Order By TopDept.Id ;
If you really want the extra department C result, you will have to union it in!