Solved

Multiple date sorting query

Posted on 2007-11-22
10
262 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:beaudoin_n
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20336951
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20336972
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20336986
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
 

Author Comment

by:beaudoin_n
ID: 20337249
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20337255
have you tried my query above? it should give you what you want
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:beaudoin_n
ID: 20337380
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20337575
i thought you meant that dates are stored in char(4) implicitly year 2007?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20337643

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
 

Author Comment

by:beaudoin_n
ID: 20338650
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
 

Author Closing Comment

by:beaudoin_n
ID: 31410606
A must have expert on your team... Superb and accurate way to fetch the results needed
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now