I already had a date_range function, so was able to do
select distinct date_trunc('month',date_range)::date as month_start from
date_range('2017-01-01'::date,'2018-02-20'::date)
which gives me a more flexible version of the month_starts function.
select distinct date_trunc('month',date_range)::date as month_start from
date_range('2017-07-01'::date,'2018-02-20'::date);
month_start
-------------
2017-07-01
2017-08-01
2017-09-01
2017-10-01
2017-11-01
2017-12-01
2018-01-01
2018-02-01
(8 rows)
I then managed to create a select to create the dataset that I want.
select d.dept_id, d.month_start, coalesce(a.target_units,d.target_units) as
target_units, coalesce(a.unit_cost,d.unit_cost) as unit_cost
from (select d.*, month_start from default_bugdet d,
(select distinct date_trunc('month',date_range)::date as
month_start from date_range('2017-09-01'::date,'2018-08-01'::date)) as c
) as d
left outer join adjustments a on a.month_start = d.month_start and a.dept_id =
d.dept_id
order by month_start;
dept_id | month_start | target_units | unit_cost
---------+-------------+--------------+-----------
1 | 2017-09-01 | 20 | 10.00
1 | 2017-10-01 | 20 | 10.00
1 | 2017-11-01 | 20 | 10.00
1 | 2017-12-01 | 20 | 10.00
1 | 2018-01-01 | 15 | 10.00
1 | 2018-02-01 | 20 | 15.00
1 | 2018-03-01 | 20 | 10.00
1 | 2018-04-01 | 20 | 10.00
1 | 2018-05-01 | 20 | 10.00
1 | 2018-06-01 | 20 | 10.00
1 | 2018-07-01 | 20 | 10.00
1 | 2018-08-01 | 20 | 10.00
(12 rows)
Is there a better way of achieving this? Is there a more efficient query I
could use?
Is there any way to create a rule for this instead of creating a function?
