All Forums Database
vniri.teradata 18 posts Joined 01/09
08 Jul 2009
Need a logic to retireve data from the table ..

Hi ,I have a table with the following dataID Dept Sal100 a 10100 b 20100 c 30100 d 40200 b 20200 c 30200 d 40300 c 30300 d 40400 d 40I 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 20c) 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*DResult: 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 40Following should be the final result..ID Dept Sal200 b 20300 c 30300 d 120400 d 40I have around 25 million records in that table.Thanks in advance..--Nick

Jimm 298 posts Joined 09/07
09 Jul 2009

You need to check your requirement against expected results!The following SQL gives the answer:Id Dept CalcSal200 b 20300 c 1200400 d 40This 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

vniri.teradata 18 posts Joined 01/09
10 Jul 2009

thanks alot Jimm... that was exactly what I wanted.Cheers--Nick

You must sign in to leave a comment.