All Forums Database
KVB 124 posts Joined 09/12
24 Jul 2014
Complex SQL Scenario

I have a table with multiple employees,the records needs to be picked up is on different conditions.
I have done them one by one and did UNION.But I am looking for any other manner.Pleas ethrow any light on this.
ID TYPE_CD EFFDT  SEQ
1 A 1-Jul-14 1
1 O 1-Jul-14 2
1 N 1-Jul-14 3
If there are codes in A,O,N then N needs to be picked up
   
2 A 5-Jul-14 4
2 A 5-Jul-14 5
2 O 5-Jul-14 6
2 N 5-Jul-14 7
If there are multiple A's followed by O and N,then maximum sequence number needs to be picked up.   
3 A 5-Jul-14 8
3 A 5-Jul-14 9
3 A 5-Jul-14 10
3 A 5-Jul-14 11
If there are multiple A's,then maximum seqid needs to be picked up.
   
4 A 5-Jul-14 12
4 D 5-Jul-14 13
If there are A and D,then this should be neglected.

ulrich 816 posts Joined 09/09
24 Jul 2014

Can you provide SQLs to set up the test data please. Will make it easier to check the options.
How do you distinct rule 1 and 2 (e.g. rule 2 example data also is also comply to rule 1). Same is true for rule 3 example data - 2 is also multiple (e.g. rule 2 example data will comply to rule 3)...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

krishaneesh 140 posts Joined 04/13
28 Jul 2014

Are the ID's constant, i mean are the rules set based on ID column. is Rule 1 fixed for ID=1 etc..

17 Feb 2015

sel * from test_dup where ( id,seq) in (

sel distinct id,seq from test_dup where type_cd in ('A','N')

and id not in 

(sel id from test_dup where type_cd='A' and type_cd='D'))

 

QUALIFY RANK() OVER( PARTITION BY id ORDER BY seq DESC) = 1

--------------------------------------------------------------------------------------------------

 

 

You must sign in to leave a comment.