From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: Monthly budgets
Date: Thu, 1 Mar 2018 09:42:38 +0000
User-Agent: KMail/1.9.10
References: <201803010842.19235.gary.stainburn@ringways.co.uk>
In-Reply-To: <201803010842.19235.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201803010942.38097.gary.stainburn@ringways.co.uk>
Precedence: bulk

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?