Home » SQL & PL/SQL » SQL & PL/SQL » get closer to datetime in two tables (oracle 12c)
get closer to datetime in two tables [message #681097] Tue, 16 June 2020 04:15 Go to next message
mape
Messages: 262
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2986
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.
Re: get closer to datetime in two tables [message #681100 is a reply to message #681099] Tue, 16 June 2020 06:00 Go to previous message
mape
Messages: 262
Registered: July 2006
Location: Slovakia
Senior Member
Yes you right, nearest means nearest to start_date.

Thanks for help
Previous Topic: The ESCAPE Option
Next Topic: 'Create table' statement is hanging
Goto Forum:
  


Current Time: Fri Sep 25 23:23:54 CDT 2020