Thread: Query how-to

<div dir="ltr">Hi,<br /><br />I was wondering if you can help me with the following query.<br /><br />Given table T1
andcolumns id, start_date, stop_date and status, propose a query that reports count of items opened and closed . status
isan enum including NEW, xxx, xxxx, CLOSED. The first status of an item is NEW (could be used in place of
start_date)<br/> For example<br /><br />Date                     Opened        Closed<br
/>=============================<br/>yyyy-mm-dd       25                   6<br />yyyy-mm-dd      
0                     16<br />yyyy-mm-dd       12                    0<br /> etc <br />etc<br /><br />Thanks<br
/>Medi<br/></div> 

On Thu, Oct 2, 2008 at 1:49 PM, Montaseri <montaseri@gmail.com> wrote:
> I was wondering if you can help me with the following query.
>
> 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
> etc

Here is one way using correlated subqueries.

SELECT A."date", ( SELECT COUNT(*)                    FROM Yourtable AS Y1                   WHERE Y1.start_date =
A."date") AS opened,      ( SELECT COUNT(*)          FROM Yourtable AS Y2         WHERE Y2.end_date = A."date" ) AS
closedFROM ( SELECT start_date AS "date"          FROM Yourtable      GROUP BY start_date         UNION        SELECT
end_dateAS "date"          FROM Yourtable      GROUP BY end_date ) AS A
 
ORDER BY A."date";


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

<br /><p><font size="2">Hi,<br /><br /> What about something like that ?<br /><br /> select adate, sum(openedCount) as
openedCount,sum(closedCount) as closedCount<br /> from<br /> (<br />   select sum(case when status ='Closed' then
stop_dateelse start_date end) as adate,<br />        sum(case when status ='Closed' then 1 else 0 end) as
closedCount<br/>        sum(case when status ='New' then 1 else 0 end) as openedCount<br />   from  Yourtable<br />  
wherestatus in ('Closed','New')<br /> )x<br /> group by adate<br /> order by adate<br /><br /> HTH,<br /><br /> Marc<br
/></font>

this was silly from me!
this should naturally look like this:
 select case when status ='Closed' then stop_date else start_date end
as adate,        sum(case when status ='Closed' then 1 else 0 end) as
closedCount,        sum(case when status ='New' then 1 else 0 end) as openedCount from  Yourtable where status in
('Closed','New')group by case when status ='Closed' then stop_date else start_date end 
 order by adate

Marc

> Hi,

> What about something like that ?

> select adate, sum(openedCount) as openedCount, sum(closedCount) as
closedCount
> from
> (
>   select sum(case when status ='Closed' then stop_date else start_date
end) as adate,
>        sum(case when status ='Closed' then 1 else 0 end) as
closedCount
>        sum(case when status ='New' then 1 else 0 end) as openedCount
>   from  Yourtable
>   where status in ('Closed','New')
> )x
> group by adate
> order by adate