Complex SQL Scenario

Database
KVB
N/A

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.

3 REPLIES
N/A

Re: Complex SQL Scenario

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)...

Re: Complex SQL Scenario

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

Re: Complex SQL Scenario

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

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