Re: Date and filling issues

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;