Re: Date and filling issues

2008/3/19, Christopher Crews <isephoenix(at)yahoo(dot)com>:
> Hi All,
> I'm not quite sure how to phrase this, but essentially my company has me
> working on some reports and I have some charts associated with the SQL
> results.
>
> My current query is:
>
> select
> transaction_date as date,
> sum(sale_amount) as sales
> from ej_transaction
> where transaction_date
> between current_date - Interval '1 month' and current_date
> group by transaction_date
> order by transaction_date asc
>
> The issue I'm having is that there are some dates where sales of certain
> items simply don't take place. Instead of putting a date entry in the
> database with a sale amount of 0, there simply is no entry for that date. I
> need to make a query that will fill in the dates not found within the date
> range and populate them with the sales value of 0.
>
> A sample of the current results data would be like
> date sales
> 2008-03-07 100.00
> 2007-03-10 150.00
> 2007-03-18 50.00
>
> and what I'm trying to do is fill in the missing dates with sales values of
> 0.
>

Try:
SELECT s.date::date, sum(COALESCE(ej_transaction.sale_amount,0)) as sales
FROM generate_series(current_date - '1 month', current_date) AS s(date)
LEFT OUTER JOIN ej_transaction
GROUP BY s.date
ORDER BY s.date ASC;

Osvaldo