Multiple date sorting query

Hi,

I have a table with 4 dates and i need to order those and keep only the good records.

Table 1
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.

Additional Details

Examples
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
beaudoin_nAsked:
Who is Participating?
 
imitchieCommented:

select r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
from (
  select rank() over (
    partition by no_tel, dt_eff_inet
    order by
    nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
    nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
    nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc)
    r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
  from cleaned_sga_tpc) a
where r = 1 and not exists (
  select 1
  from (
    select r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
    from (
      select rank() over (
        partition by no_tel, dt_eff_inet
        order by
        nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
        nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
        nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc)
        r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
      from cleaned_sga_tpc) b
    where r = 1) c
  where a.no_tel = c.no_tel
    and a.dt_eff_inet < c.dt_eff_inet
    and a.dt_deb >= c.dt_deb)

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you want to sort data, then
you can say

select number, dt1, dt2, dt3, dt4, flagkeep
from your_table
order by number, dt4 desc -- similary you can add here other columns which are reqired.

Just note that we are not filtering any records here. ( i mean we are not excluding any records because we are not using any where clause in the select statement ).

I did not understand much of your explanation given above. If you put some sample data and then say which one you want and which one you do not want, then we can add where clauses accordingly.

Thanks
0
 
imitchieCommented:
can you try the query below and see if it correctly identifies the ones you want to keep? if it does, change it to an UPDATE
select rank() over (
 partition by n, dt4
 order by
  nvl(dt3, '0000') desc,
  nvl(dt2, '9999') desc,
  nvl(dt1, '9999') desc
) as ranked, n, dt1, dt2, dt3, dt4
from table1 a
where ranked = 1 and not exists
(
 select 1 from table1 b
 where a.n = b.n
   and a.dt4 < b.dt4
   and a.dt1 >= b.dt1
)

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
imitchieCommented:
ps
>>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.

how does that explain the selection

1 0501 0630 null 0420 Y
1 0501 null null 0420 N    << shouldn't it be this one?

1, dt3 is null - rule 1 doesn't apply
2, dt3, keep greatest d2 - null is greater, therefore 2nd row

no?

also, with #3, do you mean all DT3 or all DT2? I assume you mistyped and it should be DT2
0
 
beaudoin_nAuthor Commented:
1, dt3 is null - rule 1 doesn't apply
2, dt3, keep greatest d2 - null is greater, therefore 2nd row

no?

also, with #3, do you mean all DT3 or all DT2? I assume you mistyped and it should be DT2
You are right it should be the second line !

For #3 it is if all DT2 are the same then i keep the greatest DT1
0
 
imitchieCommented:
have you tried my query above? it should give you what you want
0
 
beaudoin_nAuthor Commented:
Hi i tried and it had to change a little
to this


select no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet from
(select rank() over ( partition by no_tel, dt_eff_inet
order by
    nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
      nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
      nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc)
      r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
      from cleaned_sga_tpc  ) a where r = 1 and not exists
      ( select 1 from cleaned_sga_tpc b where a.no_tel = b.no_tel and a. dt_eff_inet < b.dt_eff_inet and a.dt_deb >= b.dt_deb)

however i don't get why it doesn't give me 2 lines (is it because r needs to be =1 ) ?
I really need the first of each greater dt4 (dt_eff_inet in real) unless the dt1 (dt_deb in real) remains the same
here is some live data
2007-02-01  2007-10-09  null 2007-11-19
2007-10-12 2007-10-11  null 2007-11-19
2007-10-13 2007-10-17 null 2007-11-19
2007-10-13 2007-10-17  null 2007-10-16
2007-10-13  null              null 2007-10-15
2007-02-01 null              null  2007-07-24
2007-02-01  null             null  2007-07-20

It returns the 3rd line which is ok.. but why isn't it returning the 6th line also

Thanks for the help :)
0
 
imitchieCommented:
i thought you meant that dates are stored in char(4) implicitly year 2007?
0
 
beaudoin_nAuthor Commented:
Thanks for all the help, unfortunatelly i wasn't logged on anymore at 2:22 EST !
I'll check it out and get back to you as soon as i can.
I can have date throughout 2004 to present (and even some dates in the near future) it was juste easier to fit the format in one line in this template so i just took examples all in the same year for practical purposes ;)


Thanks again a million times... i wish i could help out as you do !
0
 
beaudoin_nAuthor Commented:
A must have expert on your team... Superb and accurate way to fetch the results needed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.