Re: Query how-to

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