Re: SQL Advanced Query question

In article <1993Nov29.223709.19652_at_relay.nswc.navy.mil> dackerm_at_L10SERVER.NoSubdomain.NoDomain (DanielAckerman) writes:
> I have a SQL coding question. I have a table as follows:
>
> HISTORY_TABLE
> -----------------------
> UNIQ_KEY NUMBER
> STATUS VARCHAR2
> STATUS_DATE DATE
>
> I need to write a report from SQL*PLUS that will give me the counts of the
>various statuses as of each week between two dates. So the report will
>look like:
>
> DATE NEW OPEN DEFERRED
> NOV 7 3 2 2
> NOV 14 2 1 2
> NOV 21 4 1 0
>
> I do not want the number of new, opened or deferred records that occurred
>during each week but rather the total number as of each week. So between the
>week of the NOV 7 and NOV 14 one record moved from NEW to OPEN. Between
>NOV 14 and 21 two new records were added and two deferred records were closed.
>
>Any help would be greatly appreciated.
>
>Dan Ackerman

Dan - try

	SELECT status_date 'DATE'
	sum(decode(status,'NEW',1,null)) 'NEW'
	sum(decode(status,'OPEN',1,null)) 'OPEN'
	sum(decode(status,'DEFERRED',1,null)) 'DEFERRED'
	FROM history_table
	GROUP BY status_date, status
	HAVING status_date BETWEEN '06-NOV-1993' and '_at_!-NOV-1993'
	ORDER BY status_date;

Good Luck -

Joy Oberholtzer joy_at_solbourne.com
Sr. Solutions Architect
Solbourne Computer, Inc.

(303) 678-4561
(303) 678-4716 Fax Received on Thu Dec 02 1993 - 23:05:44 CET