From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Monthly budgets |
Date: | 2018-03-01 09:42:38 |
Message-ID: | 201803010942.38097.gary.stainburn@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Gary M | 2018-03-01 21:16:58 | How to best migrate this nested join ? |
Previous Message | Gary Stainburn | 2018-03-01 08:42:19 | Monthly budgets |