From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query how-to |
Date: | 2008-10-02 22:04:51 |
Message-ID: | 48E54583.8010003@sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Mamin | 2008-10-03 12:20:45 | Re: Query how-to |
Previous Message | Richard Broersma | 2008-10-02 21:10:09 | Re: Query how-to |