Montaseri wrote:
Given table T1 and columns id, start_date, stop_date and status, propose
a query that reports count of items opened and closed . status is an
enum including NEW, xxx, xxxx, CLOSED. The first status of an item is
NEW (could be used in place of start_date)
For example
Date Opened Closed
=============================
yyyy-mm-dd 25 6
yyyy-mm-dd 0 16
yyyy-mm-dd 12 0
etc
Divide and Conquer; break up a large task into smaller tasks.
1) Generate a range of dates (this one goes back 1 year):
select (now()::date+generate_series(-365,0) * interval '1 days')::date
2) How many projects were opened on each day?
select start,count(*) as open from t1 group by start
3) How many projects were opened on each day?
select stop,count(*) as closed from t1 group by stop
Now do put these all together with subselects and joins
select date,open,closed from (
select (now()::date+generate_series(-365,0) * interval '1 days')::date
) as series
left join (
select start,count(*) as open from t1 group by start
) as t2 on t2.start::date=series.date::date
left join (
select stop,count(*) as closed from t1 group by stop
) as t3 on t3.stop::date=series.date::date
where open is not null or closed is not null
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
