Home » SQL & PL/SQL » SQL & PL/SQL » Join tables on Date with MAX (Oracle 10g 10.2.0.4.0)
Join tables on Date with MAX [message #572849] Tue, 18 December 2012 02:58 Go to next message
isri
Messages: 14
Registered: November 2012
Junior Member
Hello,

I'm looking a way to easily join two tables on date value but right table has no all possible dates so I need t fill missing records with MAX(DT) values.

My tables looks like

CREATE TABLE CAL
  (DT DATE NOT NULL
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('01/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('02/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('03/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('04/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('05/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('06/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('07/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('08/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('09/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_DATE('10/01/2012','DD/MM/YYYY')
  );
CREATE TABLE SOME_DATA
  (S_DT DATE NOT NULL, S_VAL NUMBER NOT NULL
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_DATE('01/01/2012','DD/MM/YYYY'),
    '10'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_DATE('03/01/2012','DD/MM/YYYY'),
    '15'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_DATE('07/01/2012','DD/MM/YYYY'),
    '20'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_DATE('10/01/2012','DD/MM/YYYY'),
    '25'
  );


And I was trying to merge them somehow to get following result (for all missing dates from SOME_DATA I want to get value corespond to maximum data not higher than cal.dt):
 -- good result
|     CAL.DT | SOME_DATA.SOME_VAL |
-----------------------------------
| 01/01/2012 |                 10 |
| 02/01/2012 |                 10 |
| 03/01/2012 |                 15 |
| 04/01/2012 |                 15 |
| 05/01/2012 |                 15 |
| 06/01/2012 |                 15 |
| 07/01/2012 |                 20 |
| 08/01/2012 |                 20 |
| 09/01/2012 |                 20 |
| 10/01/2012 |                 25 |


But still getting
 -- example query
SELECT DT,
  (SELECT S_VAL
  FROM SOME_DATA D
  WHERE S_DT =
    (SELECT MAX(S_DT) FROM SOME_DATA WHERE S_DT = D.S_Dt
    )
  AND C.DT = D.S_DT
  ) AS SOME_VAL
FROM CAL C;


|         DT | SOME_VAL |
-------------------------
| 01/01/2012 |       10 |
| 02/01/2012 |   (null) |
| 03/01/2012 |       15 |
| 04/01/2012 |   (null) |
| 05/01/2012 |   (null) |
| 06/01/2012 |   (null) |
| 07/01/2012 |       20 |
| 08/01/2012 |   (null) |
| 09/01/2012 |   (null) |
| 10/01/2012 |       25 |


Any idea?

Many thanks and regards
Re: Join tables on Date with MAX [message #572850 is a reply to message #572849] Tue, 18 December 2012 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use MAX in its analytic form:
SQL> select cal.dt, 
  2         nvl(some_data.s_val, max(some_data.s_val) over (order by cal.dt)) val
  3  from cal left outer join some_data on (cal.dt=some_data.s_dt)
  4  order by cal.dt
  5  /
DT                         VAL
------------------- ----------
01/01/2012 00:00:00         10
02/01/2012 00:00:00         10
03/01/2012 00:00:00         15
04/01/2012 00:00:00         15
05/01/2012 00:00:00         15
06/01/2012 00:00:00         15
07/01/2012 00:00:00         20
08/01/2012 00:00:00         20
09/01/2012 00:00:00         20
10/01/2012 00:00:00         25

Regards
Michel
icon14.gif  Re: Join tables on Date with MAX [message #573368 is a reply to message #572850] Thu, 27 December 2012 06:36 Go to previous messageGo to next message
isri
Messages: 14
Registered: November 2012
Junior Member
I...'m so stupid!

Works great - many thanks for help Smile
Re: Join tables on Date with MAX [message #573407 is a reply to message #573368] Fri, 28 December 2012 03:16 Go to previous messageGo to next message
isri
Messages: 14
Registered: November 2012
Junior Member
Hello once again,

Let say my situation little change, I mean data are not always rising by date:

CREATE TABLE CAL
  (DT date NOT NULL
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('01/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('02/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('03/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('04/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('05/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('06/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('07/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('08/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('09/01/2012','DD/MM/YYYY')
  );
INSERT INTO CAL
  (DT
  ) VALUES
  (TO_date('10/01/2012','DD/MM/YYYY')
  );
CREATE TABLE SOME_DATA
  (S_DT date NOT NULL, S_VAL NUMBER NOT NULL
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_date('01/01/2012','DD/MM/YYYY'),
    '100'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_date('03/01/2012','DD/MM/YYYY'),
    '150'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_date('07/01/2012','DD/MM/YYYY'),
    '5'
  );
INSERT
INTO SOME_DATA
  (
    S_DT,
    S_VAL
  )
  VALUES
  (
    TO_date('10/01/2012','DD/MM/YYYY'),
    '10'
  );


And I want to get sth like that:

|                             DT | S_VAL |
------------------------------------------
| January, 01 2012 00:00:00+0000 |   100 |
| January, 02 2012 00:00:00+0000 |   100 |
| January, 03 2012 00:00:00+0000 |   150 |
| January, 04 2012 00:00:00+0000 |   150 |
| January, 05 2012 00:00:00+0000 |   150 |
| January, 06 2012 00:00:00+0000 |   150 |
| January, 07 2012 00:00:00+0000 |     5 |
| January, 08 2012 00:00:00+0000 |     5 |
| January, 09 2012 00:00:00+0000 |     5 |
| January, 10 2012 00:00:00+0000 |    10 |


I've get that using following query:

SELECT DT, SDATA.S_VAL
FROM CAL
LEFT JOIN
(SELECT S_DT AS SDT, LEAD(S_DT) OVER (ORDER BY S_DT) AS LSDT, S_VAL
FROM SOME_DATA
)SDATA
ON ((CAL.DT < SDATA.LSDT OR SDATA.LSDT IS NULL) AND CAL.DT >= SDATA.SDT)
ORDER BY DT;


But it seems it isn't optimal - execution time is 20 times longer. Could it be somehow optimised?

Many thanks!
Re: Join tables on Date with MAX [message #573409 is a reply to message #573407] Fri, 28 December 2012 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And I want to get sth like that:


Which is, with words?
How is this different from the previous issue?

Regards
Michel

[Updated on: Fri, 28 December 2012 03:51]

Report message to a moderator

Re: Join tables on Date with MAX [message #573412 is a reply to message #573409] Fri, 28 December 2012 04:37 Go to previous messageGo to next message
isri
Messages: 14
Registered: November 2012
Junior Member
Hi Michel,
Query you provide works fine for previous conditions - it retrieve MAX value. But it appeared that in my case values are not rising according to dates.

Query you provide works in new condition in that way:

select cal.dt, 
  nvl(some_data.s_val, max(some_data.s_val) over (order by cal.dt)) val
  from cal left outer join some_data on (cal.dt=some_data.s_dt)
  order by cal.dt;

|                             DT | VAL |
----------------------------------------
| January, 01 2012 00:00:00+0000 | 100 |
| January, 02 2012 00:00:00+0000 | 100 |
| January, 03 2012 00:00:00+0000 | 150 |
| January, 04 2012 00:00:00+0000 | 150 |
| January, 05 2012 00:00:00+0000 | 150 |
| January, 06 2012 00:00:00+0000 | 150 |
| January, 07 2012 00:00:00+0000 |   5 |
| January, 08 2012 00:00:00+0000 | 150 |
| January, 09 2012 00:00:00+0000 | 150 |
| January, 10 2012 00:00:00+0000 |  10 |


Instead of

|                             DT | S_VAL |
------------------------------------------
| January, 01 2012 00:00:00+0000 |   100 |
| January, 02 2012 00:00:00+0000 |   100 |
| January, 03 2012 00:00:00+0000 |   150 |
| January, 04 2012 00:00:00+0000 |   150 |
| January, 05 2012 00:00:00+0000 |   150 |
| January, 06 2012 00:00:00+0000 |   150 |
| January, 07 2012 00:00:00+0000 |     5 |
| January, 08 2012 00:00:00+0000 |     5 |
| January, 09 2012 00:00:00+0000 |     5 |
| January, 10 2012 00:00:00+0000 |    10 |
Re: Join tables on Date with MAX [message #573415 is a reply to message #573412] Fri, 28 December 2012 05:08 Go to previous message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I thought you wanted the max of the previous value.
You want the last of these values, so:
SQL> select cal.dt, 
  2         nvl(some_data.s_val, last_value(some_data.s_val ignore nulls) over (order by cal.dt)) val
  3  from cal left outer join some_data on (cal.dt=some_data.s_dt)
  4  order by cal.dt
  5  /
DT                         VAL
------------------- ----------
01/01/2012 00:00:00        100
02/01/2012 00:00:00        100
03/01/2012 00:00:00        150
04/01/2012 00:00:00        150
05/01/2012 00:00:00        150
06/01/2012 00:00:00        150
07/01/2012 00:00:00          5
08/01/2012 00:00:00          5
09/01/2012 00:00:00          5
10/01/2012 00:00:00         10

Regards
Michel
Previous Topic: Exception query
Next Topic: How we can run pl/sql procedure in parallel
Goto Forum:
  


Current Time: Wed Jul 30 10:26:43 CDT 2014

Total time taken to generate the page: 0.11287 seconds