Need a logic to retireve data from the table ..

Database

Need a logic to retireve data from the table ..

Hi ,

I have a table with the following data

ID Dept Sal
100 a 10
100 b 20
100 c 30
100 d 40
200 b 20
200 c 30
200 d 40
300 c 30
300 d 40
400 d 40

I want to retrieve the ID's from the above table based on the below logic..

a) If an ID is in Dept 'a' do not retrieve that ID.

b) If an ID does not exist in Dept 'a' then check if its in Dept 'B' and retrive only that row.
200 b 20

c) If an ID does not exist in Dept 'a' then check if its in Dept 'B' if not avaiable then
check for DEPT 'c' and 'd' and retrieve the Dept 'c' but Sal is calculated as sal of C*D

Result: 300 c 120 ( instead of 300 c 30 )

d) If an ID does not exist in Dept 'a' then check if its in Dept 'B' if not avaiable then
check for DEPT 'c' and 'd' , if not avaiable then and retrieve the Dept 'd'

Result: 400 d 40

Following should be the final result..
ID Dept Sal
200 b 20
300 c 30
300 d 120
400 d 40

I have around 25 million records in that table.

Thanks in advance..

--Nick

2 REPLIES
N/A

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.

SQL is:

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!

HTH

Re: Need a logic to retireve data from the table ..

thanks alot Jimm... that was exactly what I wanted.

Cheers
--Nick