SQL Advanced Query Question

> 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

Try this:

	select sum(decode(status, 'NEW', 1, 0)),
               sum(decode(status, 'OPEN', 1, 0)),
               sum(decode(status, 'DEFERRED', 1, 0)),
	       next_day(status_date, 'MONDAY')
	from   history_table
	group by next_day(status_date, 'MONDAY')
	/

You might have to vary the day in the next_day function, but this should get you most of the way there.

Regards
Greg Craigen Received on Thu Dec 02 1993 - 23:39:21 CET