Home » SQL & PL/SQL » SQL & PL/SQL » Date Range disection - Can it be Done???
icon5.gif  Date Range disection - Can it be Done??? [message #293748] Mon, 14 January 2008 18:28 Go to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
Hi ive got a requirement that looked to be easy but after a day of stuffing around and asking several other people in the office i have resorted to joining this forum in the hope someone has the simple answer for me that i know is there....

I have two tables. Each table relates to the same entity my job is to join the table into 3rdNF to create a data warehouse dimension.

The two tables are as follows

Table 1 - Airport_name

AirportKey, Airport_name ,start_date, end_date

101 , 'ADL', 1970 , 2005
101 , 'HKG', 2005 , 8888

Table 2 - Airport_owner

AirportKey, Airport_owner, start_date, end_date

101, 'ASA', 1970, 2003
101, 'RAAF' 2003, 8888

The results im looking for are as follows

AirportKey, Airport_name, Airport_owner, start_date, end_date

101 , 'ADL' , 'ASA' , 1970 , 2003
101 , 'ADL' , 'RAAF' , 2003 , 2005
101 , 'HKG' , 'RAAF' , 2005 , 8888

Of course this is a very simplified example but if i can get this correct in either PLSQL or better SQL then im OK.

Your help is greatly appreciated.

Kingsley
Re: Date Range disection - Can it be Done??? [message #293749 is a reply to message #293748] Mon, 14 January 2008 18:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the posting guidelines as stated in the URL above; including the use of <code tags> to make your columns line up & readable.
Re: Date Range disection - Can it be Done??? [message #293750 is a reply to message #293748] Mon, 14 January 2008 18:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT AO.AIRPORTKEY, AN.AIRPORT_NAME, AO.START_DATE, AO.END_DATE
FROM AIRPORT_NAME AN, AIRPORT_OWNER AO
WHERE AO.AIRPORTKEY = AN.AIRPORTKEY;

AirportKey, Airport_name ,start_date, end_date

101 ,       'ADL',         1970 ,      2005
101 ,       'HKG',         2005 ,      8888

Table 2 - Airport_owner

AirportKey, Airport_owner, start_date, end_date

101,       'ASA',          1970,       2003
101,       'RAAF'          2003,       8888

The results im looking for are as follows

AirportKey, Airport_name, Airport_owner, start_date, end_date

101 ,       'ADL' ,       'ASA' ,        1970 ,      2003
101 ,       'ADL' ,       'RAAF' ,       2003 ,      2005
101 ,       'HKG' ,       'RAAF' ,       2005 ,      8888

[Updated on: Mon, 14 January 2008 18:47] by Moderator

Report message to a moderator

Re: Date Range disection - Can it be Done??? [message #293754 is a reply to message #293750] Mon, 14 January 2008 19:09 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
You have also fallen into the trap i thought it would be that easy....

the trick with this query is the Date range problem and figuring out when one record starts and the other one ends.

The results youve shown from the query cant occur. The year 2005 doesnt apear in the AO. (Airport_owner table).


Thankyou for your effort though...any other thoughts?
Re: Date Range disection - Can it be Done??? [message #293757 is a reply to message #293748] Mon, 14 January 2008 19:20 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The results youve shown from the query cant occur.
HUH?
It is/was the results you said you wanted.

>The year 2005 doesnt apear in the AO. (Airport_owner table).
So What?

If you really expect useful responses, then FOLLOW the posting guidelines!

Provide DDL for tables.
Provide DML to load sample test data.
Provide FORMATTED expected/desired output.
Post the business rules for what should be done when "doesnt appear in the AO" & similar after the fact objections.

Re: Date Range disection - Can it be Done??? [message #293758 is a reply to message #293748] Mon, 14 January 2008 19:43 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
This is my first posting on this forum and was hoping to find help.

I realy dont know a more simple way to describe the problem than the information ive provided.

The date range issue is the problem

if i was to use this

SELECT AO.AIRPORTKEY, AN.AIRPORT_NAME, AO.START_DATE, AO.END_DATE
FROM AIRPORT_NAME AN, AIRPORT_OWNER AO
WHERE AO.AIRPORTKEY = AN.AIRPORTKEY;

I would get the following results (2 records in each table hash joined gives me 4 rows)


AirportKey, Airport_name, Airport_owner, start_date, end_date
101 , 'ADL' , 'ASA' , 1970 , 2003
101 , 'ADL' , 'RAAF' , 1970 , 2003
101 , 'HKG' , 'ASA' , 2003 , 8888
101 , 'HKG' , 'RAAF' , 2003 , 8888

the result set i need is the following

AirportKey, Airport_name, Airport_owner, start_date, end_date
101 , 'ADL' , 'ASA' , 1970 , 2003
101 , 'ADL' , 'RAAF' , 2003 , 2005
101 , 'HKG' , 'RAAF' , 2005 , 8888

As you can see there is a subtle difference in the results.

Disecting the start and end date ranges is the trick.




Re: Date Range disection - Can it be Done??? [message #293760 is a reply to message #293757] Mon, 14 January 2008 19:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I cannot try it out without test data (which I'm too lazy to build myself) so you will have to try this out yourself.
SELECT n.airport_key, n.airport_name, o.airport_owner
, GREATEST(n.start_date, o.start_date)
, LEAST(n.end_date, o.end_date)
FROM airport_name n
JOIN airport_owner o 
ON n.airport_key = o.airport_key
AND n.start_date <= o.end_date
AND n.end_date > o.start_date


Ross Leishman
Re: Date Range disection - Can it be Done??? [message #293762 is a reply to message #293748] Mon, 14 January 2008 20:02 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
rleishman your a champion.....

This solution works great.

Thankyou for reading the problem and providing me a solution that works without insulting me.

Some other people should learn from your decorum...

Good Work....

P.S. Ever wanted to move to Canberra? Smile
Re: Date Range disection - Can it be Done??? [message #293763 is a reply to message #293762] Mon, 14 January 2008 20:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look, I'll be nice to Canberra because one of the other moderators here is from there as well.

But lets just say that if I want to go somewhere with sleet but no snow, hail, and a lazy wind (wind that blows right through you rather than bothering to go around), Ballarat is closer.
Re: Date Range disection - Can it be Done??? [message #293764 is a reply to message #293748] Mon, 14 January 2008 20:27 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
Your from melbourne right?

I havent heard the expression come to sunny melbourne....:-0)
Re: Date Range disection - Can it be Done??? [message #293987 is a reply to message #293748] Tue, 15 January 2008 20:15 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
Just as a matter of interest i have done some testing on this SQL and intend to use the concept. there were some minor variations in the SQL used to allow for if the start and end dates are exactly the same. Also i needed to put an outer join on the owners side.

The query now looks like this.

SELECT   *
    FROM (SELECT n.airportkey, n.airport_name, o.airport_owner,
                 GREATEST (n.start_date,
                           NVL (o.start_date, n.start_date)
                          ) start_date,
                 LEAST (n.end_date, NVL (o.end_date, n.end_date)) end_date
            FROM airport_name n JOIN airport_owner o
                 ON n.airportkey = o.airportkey(+)
               AND n.start_date <= o.end_date(+)
               AND n.end_date > o.start_date(+)
                 )
   WHERE start_date <> end_date
ORDER BY start_date;


I have attached a file to this post with my test cases and hope it will be of use to someone someday somewhere...or maybe not...hey i had to write it anyway!

Cheers

Thanks ORAFAQ's and its members

[Mod-edit: Frank added code-tags to improve readability]

[Updated on: Wed, 16 January 2008 00:11] by Moderator

Report message to a moderator

Re: Date Range disection - Can it be Done??? [message #294195 is a reply to message #293748] Wed, 16 January 2008 15:40 Go to previous messageGo to next message
kman01
Messages: 12
Registered: January 2008
Junior Member
Hmmmm maybe i blew my trumpet too soon.

After setting out on a path of "this is how its done" and getting ready to move into production ive discovered an issue with the code and my test cases.

something i didnt consider...

Table 1 - Airport_name

AirportKey, Airport_name ,start_date, end_date

101 , 'NAME1', 1970 , 1972
101 , 'NAME2', 1972 , 1973


Table 2 - Airport_owner

AirportKey, Airport_owner, start_date, end_date

101, 'OWNER1, 1971, 1973

The results im looking for are as follows

AirportKey, Airport_name, Airport_owner, start_date, end_date

101 , 'NAME1 , , 1970 , 1971
101 , 'NAME1' , 'OWNER1', 1971 , 1972
101 , 'NAME2' , 'OWNER1', 1972 , 1973

HELP!

Thank You

Kingsley
Re: Date Range disection - Can it be Done??? [message #294217 is a reply to message #294195] Wed, 16 January 2008 20:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could have helped yourself out by enclosing the samples in [CODE]...[/CODE] tags to preserve formatting, and also by showing what your solution does return and what is wrong with that result.

As it stands, we have to install and run your sample script and compare the result to the unformatted post.

@anacedent gave you the same advice earlier.

I didn't run the script (yes, I'm that lazy), so I'm going to guess what's wrong with it: the SQL only displays periods of non-ownership when the ENTIRE period from AIRPORT_NAME does not match AIRPORT_OWNER. When there is partial overlap, you only get the overlap: not the missing period. Or in short: row 1 from your desired results does not show.

If so, that's going to be a lot harder. It seems likely to me that the resulting SQL will be difficult to follow, so this might be better implemented in PL/SQL.

Having said that, let's have a go:

SELECT airport_key, start_date dt FROM airport_name
UNION SELECT airport_key, end_date dt FROM airport_name
UNION SELECT airport_key, start_date dt FROM airport_owner
UNION SELECT airport_key, end_date dt FROM airport_owner

should give:
101 1970
101 1971
101 1972
101 1973


Now lets convert that list to a set of contiguous periods:
WITH dates AS (
    SELECT airport_key, start_date dt FROM airport_name
    UNION SELECT airport_key, end_date dt FROM airport_name
    UNION SELECT airport_key, start_date dt FROM airport_owner
    UNION SELECT airport_key, end_date dt FROM airport_owner
)
SELECT LAG(dt) OVER (partition by airport_key, order by dt) AS start_date, dt AS end_date
FROM   dates

should give:
101      1970
101 1970 1971
101 1971 1972
101 1972 1973


So now, join some details to these periods:
WITH dates AS (
    SELECT airport_key, start_date dt FROM airport_name
    UNION SELECT airport_key, end_date dt FROM airport_name
    UNION SELECT airport_key, start_date dt FROM airport_owner
    UNION SELECT airport_key, end_date dt FROM airport_owner
)
, periods AS (
    SELECT LAG(dt) OVER (partition by airport_key, order by dt) AS start_date, dt AS end_date
    FROM   dates
)
SELECT n.airport_key, n.airport_name, o.airport_owner
,      p.start_date, p.end_date
FROM   periods p
JOIN airport_name n
ON   n.airport_key = p.airport_key
AND  n.start_date < p.end_date
AND  n.end_date > p.start_date
LEFT JOIN airport_owner o
ON   o.airport_key = p.airport_key
AND  o.start_date < p.end_date
AND  o.end_date > p.start_date


This is untested code of course, but it demonstrates the idea.

Ross Leishman
Re: Date Range disection - Can it be Done??? [message #294221 is a reply to message #293748] Wed, 16 January 2008 21:30 Go to previous message
kman01
Messages: 12
Registered: January 2008
Junior Member
Ive said it before...and i hope to say it again....

Your a legend!

[Updated on: Wed, 16 January 2008 21:31]

Report message to a moderator

Previous Topic: Bind Variable Name to Cursor
Next Topic: Help with Datetime
Goto Forum:
  


Current Time: Sat Dec 03 08:18:31 CST 2016

Total time taken to generate the page: 0.07535 seconds