Home » SQL & PL/SQL » SQL & PL/SQL » pair date range from 2 tabs
pair date range from 2 tabs [message #269707] Mon, 24 September 2007 04:11 Go to next message
mape
Messages: 241
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


Re: pair date range from 2 tabs [message #270148 is a reply to message #269707] Tue, 25 September 2007 22:47 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: pair date range from 2 tabs [message #270233 is a reply to message #269707] Wed, 26 September 2007 04:08 Go to previous message
mape
Messages: 241
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
Previous Topic: utl_file : invalid_path error
Next Topic: select where is null
Goto Forum:
  


Current Time: Sat Dec 10 22:14:08 CST 2016

Total time taken to generate the page: 0.09986 seconds