Re: SQL Advanced Query question

From: Mark Wick <mwick_at_oberon.com>
Date: Tue, 30 Nov 1993 19:07:48 GMT
Message-ID: <MWICK.93Nov30140748_at_tamora.oberon.com>

>>>>> On Mon, 29 Nov 1993 22:37:09 GMT, dackerm_at_L10SERVER.NoSubdomain.NoDomain (DanielAckerman) said:

D> I have a SQL coding question. I have a table as follows:

D> HISTORY_TABLE
D> -----------------------
D> UNIQ_KEY      NUMBER
D> STATUS        VARCHAR2
D> STATUS_DATE   DATE

D> I need to write a report from SQL*PLUS that will give me the counts of the

D> various statuses as of each week between two dates. So the report will D> look like:

D> DATE     NEW      OPEN     DEFERRED
D> NOV 7     3        2         2
D> NOV 14    2        1         2
D> NOV 21    4        1         0

D> I do not want the number of new, opened or deferred records that occurred
D> during each week but rather the total number as of each week.  So between the

D> week of the NOV 7 and NOV 14 one record moved from NEW to OPEN. Between D> NOV 14 and 21 two new records were added and two deferred records were closed.

Two aspects of this problem: first, you want columns based on stored data. Thus, you won't be able to add new status types and have the query update automagically - you'll have to do it yourself. Secondly, you have to do nasty date arithmetic. So, given some starting Date, like '07-NOV-93' what you want is:

SELECT TO_DATE('07-NOV-93') +

          week_adjustment_formula DATE
       SUM (DECODE (STATUS, 'NEW', 1, 0)) NEW,
       SUM (DECODE (STATUS, 'OPEN', 1, 0)) OPEN,
       SUM (DECODE (STATUS, 'DEFERRED', 1, 0)) DEFERRED,
       (etc)

FROM HISTORY_TABLE
GROUP BY TO_DATE('07-NOV-93') + week_adjustment_formula

week_adjustment_formula is simply:

7 * TRUNC((TO_CHAR(STATUS_DATE, 'J') - TO_CHAR(TO_DATE('07-NOV-93'), 'J')) / 7) You can change the starting date to whatever you want, obviously; if you set it equal to a number between 0 and 6, you can vary the day of the week that the week starts on, and have this query be completely independent of a starting date.

Good luck,

Mark         

--

==========================================+===================================
Mark Wick                                 | VOICE:           617-494-0990
Senior Software Engineer                  |                  617-494-5449 x171
Oberon Software, Inc.                     | FAX:             617-494-0414
One Cambridge Center, Cambridge, MA 02142 | INTERNET:        mwick_at_oberon.com
------------------------------------------+-----------------------------------
  -- I understand about indecision, I don't care if I get behind
     People living in competition - all I want is to have my peace of mind.
==========================================+===================================

Received on Tue Nov 30 1993 - 20:07:48 CET