agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Monthly budgets
Date: Thu, 1 Mar 2018 09:42:38 +0000
Message-ID: <201803010942.38097.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <201803010842.19235.gary.stainburn@ringways.co.uk>
References: <201803010842.19235.gary.stainburn@ringways.co.uk>
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?
view thread (2+ messages)
Message-ID: <201803010942.38097.gary.stainburn@ringways.co.uk>
Permalink: ../201803010942.38097.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201803010942.38097.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Monthly budgets
In-Reply-To: <201803010942.38097.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox