CODEWITHSUNDEEP
sqljoinfirebird
gdekoker
gdekoker

Reputation: 195

Join Two Tables based on a Date Range

I have two tables in my Firebird DB. The first table is called LEDGER and is showing the details of my transaction, including the date of of a transaction (yyyy/mm/dd). The second table is called PERIODS and consists of the following fields:

I want use the date as per the LEDGER table to look up the corresponding period from the PERIODS table and join the two tables based on that. I short, I want to show the period for each line of the ledger table to assist me in preparing consolidated reporting.

Herewith an example of the PERIODS table:

enter image description here

What would be the best approach to do this?

If I was the developer I would have added the period to the ledger table and populate the period on transactional level but unfortunately it is too late for that now.

Any assistance will be greatly appreciated.

Upvotes: 0

Views: 280

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141

Use below query. Please change the columns names, i have provided the column names as an example.

select P.PER, L.* from LEDGER L
inner join PERIODS P
on (L.date_of_transaction = P.start_date);



select P.PER, L.* from LEDGER L
inner join PERIODS P
on (L.date_of_transaction between P.start_date);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can just use join:

select l.*, p.per
from ledger l left join
     periods p
     on l.date_of_transaction >= p.start_date and
        l.date_of_transaction <= p.end_date

Upvotes: 2

Related Questions