OraFAQ Forum: SQL & PL/SQL » pair date range from 2 tabs

Home » SQL & PL/SQL » SQL & PL/SQL » pair date range from 2 tabs

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

pair date range from 2 tabs [message #269707]

Mon, 24 September 2007 04:11

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hi

Can anybody help me to figure join problem out?

I would like to pair records from 2 tables comparing dates.
SQL query looks like:

SELECT plh.rowid plh_rid, ne.rowid ne_rid, plh.contract, plh.load_amount, ne.startdatetime, plh.account_date,
                  FROM etdw.load_history plh, events ne
                  WHERE ne.contract = plh.contract
                  AND ne.date_key = 20070918
                  AND ne.amount = plh.load_amount
                  and plh.CONTRACT=213321610
                  and ne.STARTDATETIME >= plh.ACCOUNT_DATE-(160) / (24 * 60 * 60)
                  and ne.STARTDATETIME<=plh.ACCOUNT_DATE + (160) / (24 * 60 * 60 );

The result is:

PLH_RID	            NE_RID	            CONTRACT LOAD_AMOUNT	STARTDATETIME	    ACCOUNT_DATE	
ABKtJlAAXAAOjNxABF	ABMcCyAABAAISc7AAG	213321610	-0,35	    18.9.2007 13:39:41	18.9.2007 13:39:54	
ABKtJlAAXAAOjNxAA9	ABMcCyAABAAISc7AAG	213321610	-0,35	    18.9.2007 13:39:41	18.9.2007 13:39:49	
ABKtJlAAXAAOjNxABF	ABMcCyAABAAISdrAAO	213321610	-0,35	    18.9.2007 13:39:54	18.9.2007 13:39:54	
ABKtJlAAXAAOjNxAA9	ABMcCyAABAAISdrAAO	213321610	-0,35	    18.9.2007 13:39:54	18.9.2007 13:39:49

I would like to get only two records from query
where startdatetime '18.9.2007 13:39:41' should be joined with '18.9.2007 13:39:49' (from account_date)
and the last date '18.9.2007 13:39:54' should be joined with '18.9.2007 13:39:54'.

Thanks for help.

Martin

Report message to a moderator

Re: pair date range from 2 tabs [message #270148 is a reply to message #269707]

Tue, 25 September 2007 22:47

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

If your basis for picking those joins is to get the CLOSEST of 2 or more potential matches, then you want to MINIMISE:

abs(ne.startdatetime - plh.account_date)

One way would be (untested code):

SELECT plh.rowid plh_rid
, ne.rowid ne_rid
, plh.contract
, plh.load_amount
, ne.startdatetime
, MIN(plh.account_date) 
  KEEP (DENSE_RANK FIRST ORDER BY abs(ne.startdatetime - plh.account_date)) AS account_date
FROM etdw.load_history plh, events ne
WHERE ne.contract = plh.contract
AND ne.date_key = 20070918
AND ne.amount = plh.load_amount
and plh.CONTRACT=213321610
and ne.STARTDATETIME >= plh.ACCOUNT_DATE-(160) / (24 * 60 * 60)
and ne.STARTDATETIME<=plh.ACCOUNT_DATE + (160) / (24 * 60 * 60 )
GROUP BY plh.rowid
, ne.rowid
, plh.contract
, plh.load_amount
, ne.startdatetime;

This wouldn't work if you had two rows with the same date and time though. If that were the case, you would have to revert to a sub-query or analytic function.

Ross Leishman

Report message to a moderator

Re: pair date range from 2 tabs [message #270233 is a reply to message #269707]

Wed, 26 September 2007 04:08

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

It doesn't work exactly cause I got 2 rows with the same date and time.

Anyway thanks for advice. I think it's a good way how to tackle query. Maybe use some of the analytic functions.

Martin

Report message to a moderator

Previous Topic:	utl_file : invalid_path error
Next Topic:	select where is null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 18:20:37 CST 2024