Query Matrix.....Complex?
9336Mar 14 2006 — edited Mar 14 2006Hi,
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.