Solved

Multiple date sorting query

Posted on 2007-11-22
10
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

719 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