Join two tables in SQL with date condition

Question

In the first table, I have client number, start date and other columns:

Client number	start date	other1	other2
xyz	2024-04-01	values10	values20
abc	2024-01-02	values11	values21
ert	2024-01-03	values12	values22

In the second table, I have information about client bank account for everyday:

Date	Client number	account balance
2024-03-01	xyz	500.0000
2024-04-01	xyz	1000.0000
2024-04-02	xyz	1000.0000
2024-04-03	xyz	1200.0000
2024-04-01	abc	2200.0000
2024-04-02	abc	2300.0000
2024-04-03	abc	2400.0000

I want to join table 1 and table 2 to get information about client account from the start date from table 1.

For example client xyz has start date 2024-04-01, so in my result set, I want to have:

Date	Client number	account balance
2024-04-01	xyz	1000$
2024-04-02	xyz	1000$
2024-04-03	xyz	1200$
2024-04-02	abc	2300$
2024-04-03	abc	2400$

For client xyz I don't want information before 2024-04-01, and for client abc, I don't want information before 2024-04-02.

Problem is that I don't know what conditions to provide to limit client in the second table in relation to the start date.

I do not know how to build key to join tables with date condition

Maybe this fiddle will help us collaborate on a solution dbfiddle.uk/WL8ON_Ff — Bart McEndree
– Bart McEndree, Commented Apr 12, 2024 at 15:20
Please edit your question to include a tag for the RDMS you are using (Sql-Server, MySQL, Oracle,..) — Bart McEndree
– Bart McEndree, Commented Apr 12, 2024 at 15:35

Bart McEndree · Accepted Answer · 2024-04-12 16:06:31Z

0

I think this will provide the limits you want

WITH LimitedData as
(
  SELECT * 
  FROM SecondTable
  WHERE [Date] Between '2024-1-1' and '2025-1-1' --restrict data from 2024 only
  AND  [client number] in ('abc','xyz') --restrict to short list of clients being investigated
)
Select f.*, s.[date], s.[account balance]
FROM FirstTable f
INNER JOIN LimitedData s ON f.[client number]=s.[client number]
WHERE s.[Date] >= f.[Start date]
ORDER BY f.[client number], s.[Date]

fiddle

edited Apr 12, 2024 at 16:06

answered Apr 12, 2024 at 15:33

Bart McEndree

3,6391 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kings2407 Over a year ago

It looks ok but in my second table i have a lot of data (many clients, from long time) so i think i should limit this data to clients and date from first table befor join is make. And in final table i want all columns from the first table. The question is how to select date from secon table to put them condiotion to limit clients.

Bart McEndree Over a year ago

Edited to use LimitedData CTE and use table alias on all columns.

Jonas Metzler Over a year ago

@Kings2407 In your question, the expected result contains only columns of table2 and it is absolutely unclear if/why you need table1 at all in your query. Now in your comment, you suddenly say your result should also include columns from table1. Please think about what you really want to do and then edit your question accordingly. People can't help you if you don't know or don't say what you want to do.

Fred Over a year ago

Unless you know some filter criteria in advance, the database should figure out which table is larger and which is smaller and build the query plan accordingly. Also looks like you may want cumulative SUM("account balance") OVER (PARTITION BY "client number" ORDER BY "date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) in the outer query.

Collectives™ on Stack Overflow

Join two tables in SQL with date condition

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related