[SQL] Query how-to

[Montaseri]

Hi,

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, , CLOSED. The first status of an item is NEW (could
be used in place of start_date)
For example

Date OpenedClosed
=
-mm-dd   25   6
-mm-dd   0  16
-mm-dd   120
etc
etc

Thanks
Medi

Re: [SQL] Query how-to

[Richard Broersma]

On Thu, Oct 2, 2008 at 1:49 PM, Montaseri <[EMAIL PROTECTED]> 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, , CLOSED. The first status of an item is NEW (could
> be used in place of start_date)
> For example
>
> Date OpenedClosed
> =
> -mm-dd   25   6
> -mm-dd   0  16
> -mm-dd   120
> 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 closed
  FROM ( SELECT start_date AS "date"
   FROM Yourtable
   GROUP BY start_date
  UNION
 SELECT end_date AS "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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Query how-to

[Frank Bax]

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, , CLOSED. The first status of an item is 
NEW (could be used in place of start_date)

For example

Date OpenedClosed
=
-mm-dd   256
-mm-dd   0 16
-mm-dd   120
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