SQL Query to Get Valid Data on Given Date

Question

I have database that tracked person salary over time like table below:

I want to query the person (based on id) salary each month to give output like the table below

I don't know what query to use since it needs to iterate in the salary database to check what is the valid salary for certain date. Any idea for this?

Thanks!

Answer 1

This is a convenient place to use a lateral join. The following goes by the first day of the month rather than the last day -- because that is simpler to generate:

select i.id, gs.mon, s.salary
from generate_series('2019-01-01'::date, '2020-12-01'::date, interval '1 month') gs(mon) cross join
    (select distinct id from salaries) i left join lateral
    (select s.salary
     from salaries s
     where s.id = i.id and s.datevaliduntil >= gs.mon
     order by s.datevaliduntil asc
     limit 1
    ) s;

Of course, you can just subtract 1 day from each date if you want the last day.