pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate and filling issues
4+ messages / 4 participants
[nested] [flat]
* Date and filling issues
@ 2008-03-19 19:02 Christopher Crews <isephoenix@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Christopher Crews @ 2008-03-19 19:02 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks,
-CC
---------------------------------
Never miss a thing. Make Yahoo your homepage.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date and filling issues
@ 2008-03-20 02:36 Osvaldo Kussama <osvaldo.kussama@gmail.com>
parent: Christopher Crews <isephoenix@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Osvaldo Kussama @ 2008-03-20 02:36 UTC (permalink / raw)
To: Christopher Crews <isephoenix@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date and filling issues
@ 2008-03-20 03:03 Rodrigo E. De León Plicet <rdeleonp@gmail.com>
parent: Christopher Crews <isephoenix@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Rodrigo E. De León Plicet @ 2008-03-20 03:03 UTC (permalink / raw)
To: Christopher Crews <isephoenix@yahoo.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date and filling issues
@ 2008-03-20 23:02 chester c young <chestercyoung@yahoo.com>
parent: Christopher Crews <isephoenix@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: chester c young @ 2008-03-20 23:02 UTC (permalink / raw)
To: Christopher Crews <isephoenix@yahoo.com>; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-03-20 23:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-19 19:02 Date and filling issues Christopher Crews <isephoenix@yahoo.com>
2008-03-20 02:36 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2008-03-20 03:03 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-03-20 23:02 ` chester c young <chestercyoung@yahoo.com>
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