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.
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)...
SELECT ID, TYPE_CD, EFF_DT, SEQ FROM TEST_DUP WHERE ID NOT IN (SELECT ID FROM TEST_DUP WHERE TYPE_CD IN ('D')) QUALIFY RANK() OVER(PARTITION BY ID ORDER BY SEQ DESC) = 1;