Обсуждение: Date and filling issues

Hi All,<br />I'm not quite sure how to phrase this, but essentially my company has me working on some reports and I
havesome charts associated with the SQL results. <br /><br />My current query is:<br /><br />select <br />   
transaction_dateas date, <br />    sum(sale_amount) as sales<br />from ej_transaction<br /> where transaction_date <br
/>betweencurrent_date - Interval '1 month' and current_date<br />group by transaction_date <br />order by
transaction_dateasc<br /><br />The issue I'm having is that there are some dates where sales of certain items simply
don'ttake place. Instead of putting a date entry in the database with a sale amount of 0, there simply is no entry for
thatdate. I need to make a query that will fill in the dates not found within the date range and populate them with the
salesvalue of 0.<br /><br />A sample of the current results data would be like<br />date                    sales<br
/>2008-03-07        100.00<br />2007-03-10         150.00<br />2007-03-18         50.00<br /><br />and what I'm trying
todo is fill in the missing dates with sales values of 0.<br /><br />Thanks,<br />-CC<br /><br /><p><hr size="1"
/>Nevermiss a thing. <a href="http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs"> Make Yahoo your
homepage.</a>

2008/3/19, Christopher Crews <isephoenix@yahoo.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

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;

> 
> 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.

what I do is have a table called days that is the left side of a left
join.

I'm not proud of this, but it works well.


     ____________________________________________________________________________________
Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping