Query Matrix.....Complex?

From: Miggins <mtproc_at_yahoo.co.uk>
Date: 14 Mar 2006 04:29:39 -0800
Message-ID: <1142339379.151213.82520@i39g2000cwa.googlegroups.com>

Hi,

I have a table called SUBS

SUBS_ID                   number(8)
SUBS_RATE             varchar2(3)
SUBS_START            date
SUBS_END               date

SUBS_END_REASON varchar2(3)

Am wanting to extract active and cancelled subscriptions. Each row returned containing the total numbers for each month by rate. So for example if there were 3 different subs_rate values of FM, HM and SM it would return the following broken down by month.

Month     FM     HM     SM        FM      HM     SM
JAN 06    99      12       45         1         1        4
FEB 06   96       10       49         3        2         0
MAR 06  95        8        42         3        2         7

The first set of 3 column totals are active subscriptions in that month ie where SUBS_START is less than month start and SUBS_END is null. The second set of three rate columns are those cancellations in that month ie with a SUBS_END falling in the month and a non-null SUBS_END_REASON.

Am running of version 9.2.0.4.

Any help greatly appreciated. Received on Tue Mar 14 2006 - 06:29:39 CST