Solved

Multiple date sorting query

Posted on 2007-11-22
10
269 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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