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  |
 |
isri
Messages: 12
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 #573407 is a reply to message #573368] |
Fri, 28 December 2012 03:16  |
|
isri
Messages: 12
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 #573412 is a reply to message #573409] |
Fri, 28 December 2012 04:37 |
|
isri
Messages: 12
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 |
 |
Michel Cadot
Messages: 54253
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
|
|
|
|
Goto Forum:
Current Time: Sun May 26 03:48:31 CDT 2013
Total time taken to generate the page: 0.11926 seconds
|
] 
Members
Search
Help
Register
Login
Home
