From: Rodrigo E. De León Plicet <rdeleonp@gmail.com>
To: Christopher Crews <isephoenix@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date and filling issues
Date: Wed, 19 Mar 2008 22:03:58 -0500
Message-ID: <a55915760803192003i1acaa617x3644575772e7e030@mail.gmail.com> (raw)
In-Reply-To: <687096.56737.qm@web52202.mail.re2.yahoo.com>
References: <687096.56737.qm@web52202.mail.re2.yahoo.com>
On 3/19/08, Christopher Crews <isephoenix@yahoo.com> wrote:
> and what I'm trying to do is fill in the missing dates with sales values of 0.
create or replace function gen_dates(sd date, ed date)
returns setof date as $$
select $1 + i
from generate_series(0, $2 - $1) i;
$$ language sql immutable;
select d.date, sum(coalesce(sale_amount,0)) as sales
from gen_dates((current_date - interval '1 month')::date, current_date) d(date)
left join ej_transaction
on transaction_date=d.date
group by d.date
order by d.date asc;
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: rdeleonp@gmail.com, isephoenix@yahoo.com
Subject: Re: Date and filling issues
In-Reply-To: <a55915760803192003i1acaa617x3644575772e7e030@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox