get closer to datetime in two tables [message #681097] |
Tue, 16 June 2020 04:15 |
mape
Messages: 298 Registered: July 2006 Location: Slovakia
|
Senior Member |
|
|
Hello
I would like to sort out how to get a unique records from two tables joined by ID and comparing date.
For instance:
create table t1 (id number, start_date date);
insert into t1 (1234, 19.05.2020 02:10:07 );
create table t2(id number, start_date date, end_date date, balance);
insert into t2 (1234, 14.02.2021 14:33:00, 19.02.2020 14:33:00, 1);
insert into t2 (1234, 19.02.2020 14:33:00, 20.03.2020 14:30:00, 2);
Join tables are by ID, but I need a get right balance from table t2
where t1.start_date is the nearest value from t2 between start_date and end_date.
Does anybody know how to make a right select ??
Thanks
Mape
|
|
|
Re: get closer to datetime in two tables [message #681098 is a reply to message #681097] |
Tue, 16 June 2020 05:30 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:the nearest value from t2 between start_date and end_date.
Do you mean there can be several rows in t2 where t1.start_date may be between t2.start_date and t2.end_date?
That is, there are in t2 intervals (start_date,end_date) that overlap? Something your data do not show.
What would be the result for the data you gave? And why?
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
[Updated on: Tue, 16 June 2020 05:32] Report message to a moderator
|
|
|
Re: get closer to datetime in two tables [message #681099 is a reply to message #681097] |
Tue, 16 June 2020 05:35 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Your sample is bad. Start date in
insert into t2 values(1234, 14.02.2021 14:33:00, 19.02.2020 14:33:00, 1);
is greater than end date. I will assume you meant
insert into t2 values(1234, 14.02.2020 14:33:00, 19.02.2021 14:33:00, 1);
And t1.start_date of 19.05.2020 02:10:07 isn't between t2.start_date and t2.end_date in
insert into t2 values(1234, 19.02.2020 14:33:00, 20.03.2020 14:30:00, 2);
I will change it to
insert into t2 values(1234, 19.02.2020 14:33:00, 20.09.2020 14:30:00, 2);
I will assume nearest means nearest to start_date. Also, I'll assume if there is a tie (more than one nearest row) you want all such rows:
SQL> SELECT *
2 FROM T1
3 /
ID START_DATE
---------- -------------------
1234 19.05.2020 02:10:07
SQL> SELECT *
2 FROM T2
3 /
ID START_DATE END_DATE BALANCE
---------- ------------------- ------------------- ----------
1234 14.02.2020 14:33:00 19.02.2021 14:33:00 1
1234 19.02.2020 14:33:00 20.09.2020 14:30:00 2
SQL>
WITH T AS (
SELECT T2.*,
DENSE_RANK() OVER(
PARTITION BY T2.ID
ORDER BY T2.START_DATE - T1.START_DATE
) RNK
FROM T1,
T2
WHERE T2.ID = T1.ID
AND T1.START_DATE BETWEEN T2.START_DATE AND T2.END_DATE
)
SELECT ID,
START_DATE,
END_DATE,
BALANCE
FROM T
WHERE RNK = 1
/
ID START_DATE END_DATE BALANCE
---------- ------------------- ------------------- ----------
1234 14.02.2020 14:33:00 19.02.2021 14:33:00 1
SQL>
SY.
|
|
|
|