Multiple date sorting query
Posted on 2007-11-22
I have a table with 4 dates and i need to order those and keep only the good records.
number dt1 dt2 dt3 dt4 flagkeep
The logics are that we need to sort by number then dt4 needs to be sorted greatest date first but the dates can be the same.
If they are i need to keep the records where the dt3 is not null. If there is no dt3 i need to keep the one with the greatest d2 (null is greater).
If all dt3 are the same then i need to keep the one where dt1 is greater.
Then i i go to another record where dt4 is different (lower) if the dt1 is the same i don't want to keep this record but if dt1 is different but greater than any dt1 of the records i already kept (with dt4 greater)i don't want the record either.
dates all in 2007 format mmdd
n dt1 dt2 dt3 dt4 flagkeep
1 0701 null 0701 0630 Y
1 0701 null null 0630 N
1 0501 0630 null 0420 Y
1 0501 null null 0420 N
1 0501 null null 0401 N
1 0525 null null 0327 N