one query question

[jane]

HI,

I was ask to do a query to get the member active condition

I had table to show the member number and active status in three
month.

ACC A1 A2 A3
---- ----------- ----------- -----------
c001 1 1 1
c002 1 1 1
c003 1 1 0
c004 1 1 0
c005 1 0 0
c006 1 0 1

the analyser request following data

Sample like:

Jan 6 active memeber

Feb 4 active member 2 inactive member

Mar (2 active memeber + 2 inactive memeber under Feb 60 active
member ) (1 active memeber+1 inactive memeber under Feb 40 inactive
member)

Is there any way to do that?

I could use similar query "select a1,a2,a3 ,count(acc) group by
a1,a2,a3 to get the each level info" but how to conbine each level
info into tree info?

Thanks.

[jane]

es**********@ya hoo.com (jane) wrote in message news:<db******* *************** ****@posting.go ogle.com>...

HI,

I was ask to do a query to get the member active condition

I had table to show the member number and active status in three
month.

ACC A1 A2 A3
---- ----------- ----------- -----------
c001 1 1 1
c002 1 1 1
c003 1 1 0
c004 1 1 0
c005 1 0 0
c006 1 0 1

the analyser request following data

Sample like:

Jan 6 active memeber

Feb 4 active member 2 inactive member

Mar (2 active memeber + 2 inactive memeber under Feb 60 active
member ) (1 active memeber+1 inactive memeber under Feb 40 inactive
member)

Is there any way to do that?

I could use similar query "select a1,a2,a3 ,count(acc) group by
a1,a2,a3 to get the each level info" but how to conbine each level
info into tree info?

Thanks.

I think maybe I should explain my question more clearly.
example:
If there are 6 active at Jan. 2003 , 4 of 6-Jan-A are active at Feb, 2
of 6-Jan-A are not active at Feb. and 3 of 4-Feb-A are active at Mar,
1 of 4-Feb-A is not active at Mar. 2 of 2-Feb-NA is active in Mar.

So, the table is like

mem_no Jan Feb Mar
c001 1 1 1
c002 1 1 1
c003 1 1 1
c004 1 1 0
c005 1 0 1
c006 1 0 1
I hope get following three layer diagram like a b-tree. Active at
left, noactive at right.

--Jan 6

--Feb 4 2
--Mar 3 1 2 0

any suggestion is so appreciated.

Thanks.

[Tokunaga T.]

> So, the table is like

mem_no Jan Feb Mar
c001 1 1 1
c002 1 1 1
c003 1 1 1
c004 1 1 0
c005 1 0 1
c006 1 0 1
I hope get following three layer diagram like a b-tree. Active at
left, noactive at right.

--Jan 6

--Feb 4 2
--Mar 3 1 2 0

------------------------------ Command Entered ------------------------------
SELECT Month_Name , Layer_Diagram
FROM (
SELECT 'Jan' , ' ' || CHAR(SMALLINT(S UM( Jan )))
|| ' ' || CHAR(SMALLINT(S UM( 1-Jan )))
FROM MemberActiveSta tus
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(S UM( Jan * Feb )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) )))
FROM MemberActiveSta tus
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(S UM( Jan * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) * (1-Mar) )))
FROM MemberActiveSta tus
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Di agram)
;
-----------------------------------------------------------------------------

MONTH_NAME LAYER_DIAGRAM
---------- -------------------------------------------------------
Jan 6 0
Feb 4 2 0 0
Mar 3 1 2 0 0 0 0 0

3 record(s) selected.

[jane]

to*****@jp.ibm. com (Tokunaga T.) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

So, the table is like

mem_no Jan Feb Mar
c001 1 1 1
c002 1 1 1
c003 1 1 1
c004 1 1 0
c005 1 0 1
c006 1 0 1
I hope get following three layer diagram like a b-tree. Active at
left, noactive at right.

--Jan 6

--Feb 4 2
--Mar 3 1 2 0

------------------------------ Command Entered ------------------------------
SELECT Month_Name , Layer_Diagram
FROM (
SELECT 'Jan' , ' ' || CHAR(SMALLINT(S UM( Jan )))
|| ' ' || CHAR(SMALLINT(S UM( 1-Jan )))
FROM MemberActiveSta tus
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(S UM( Jan * Feb )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) )))
FROM MemberActiveSta tus
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(S UM( Jan * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(S UM( (1-Jan) * (1-Feb) * (1-Mar) )))
FROM MemberActiveSta tus
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Di agram)
;
-----------------------------------------------------------------------------

MONTH_NAME LAYER_DIAGRAM
---------- -------------------------------------------------------
Jan 6 0
Feb 4 2 0 0
Mar 3 1 2 0 0 0 0 0

3 record(s) selected.

HI,

Thanks so much for your input.

I made a little change on your query

SELECT Month_Name , Layer_Diagram
FROM (
SELECT 'Jan' , ' ' || CHAR(SMALLINT(S UM( Jan
)))
FROM test_e
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(S UM( Jan * Feb
)))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb)
)))
FROM test_e
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(S UM( Jan * Feb * Mar
)))
|| ' ' || CHAR(SMALLINT(S UM( Jan * Feb *
(1-Mar) )))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) * Mar
)))
|| ' ' || CHAR(SMALLINT(S UM( Jan * (1-Feb) *
(1-Mar) )))
FROM test_e
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Di agram)
;

I got the exactly result I want.

Another questions is it is too much edit work if I want to have 6 or
more layer. It is terrible to do the edit work and format the result.

[Tokunaga T.]

> Another questions is it is too much edit work if I want to have 6 or

more layer. It is terrible to do the edit work and format the result.

How about this?
------------------------------ Command Entered ------------------------------
SELECT C.Jan , C.Feb , C.Mar
, COUNT(mem_no) Counts
FROM (VALUES (1,1,1) , (1,1,0) , (1,0,1) , (1,0,0)
) AS C (Jan, Feb, Mar)
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON C.Jan = M.Jan
AND C.Feb = M.Feb
AND C.Mar = M.Mar
GROUP BY GROUPING SETS ( (C.Jan)
, (C.Jan, C.Feb)
, (C.Jan, C.Feb, C.Mar) )
ORDER BY C.Jan desc , C.Feb desc , C.Mar desc
;
-----------------------------------------------------------------------------

JAN FEB MAR COUNTS
----------- ----------- ----------- -----------
1 - - 6
1 1 - 4
1 1 1 3
1 1 0 1
1 0 - 2
1 0 1 2
1 0 0 0

7 record(s) selected.

[Tokunaga T.]

For less than 6 layers:
(The sample is for 3 layers. Modify ON conditions and add GROUP BY
columns and ORDER BY columns, for 4, 5 and 6 layers.)
------------------------------ Command Entered
------------------------------
SELECT C.Jan , C.Feb , C.Mar
, COUNT(mem_no) Counts
FROM (VALUES
(1,1,1,1,1,1), (1,1,1,1,1,0), (1,1,1,1,0,1),
(1,1,1,1,0,0)
, (1,1,1,0,1,1), (1,1,1,0,1,0), (1,1,1,0,0,1),
(1,1,1,0,0,0)
, (1,1,0,1,1,1), (1,1,0,1,1,0), (1,1,0,1,0,1),
(1,1,0,1,0,0)
, (1,1,0,0,1,1), (1,1,0,0,1,0), (1,1,0,0,0,1),
(1,1,0,0,0,0)
, (1,0,1,1,1,1), (1,0,1,1,1,0), (1,0,1,1,0,1),
(1,0,1,1,0,0)
, (1,0,1,0,1,1), (1,0,1,0,1,0), (1,0,1,0,0,1),
(1,0,1,0,0,0)
, (1,0,0,1,1,1), (1,0,0,1,1,0), (1,0,0,1,0,1),
(1,0,0,1,0,0)
, (1,0,0,0,1,1), (1,0,0,0,1,0), (1,0,0,0,0,1),
(1,0,0,0,0,0)
) AS C (Jan, Feb, Mar, Apr, May, Jun)
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON C.Jan = M.Jan
AND C.Feb = M.Feb
AND C.Mar = M.Mar
AND C.Apr = 0
AND C.May = 0
AND C.Jun = 0
GROUP BY GROUPING SETS ( (C.Jan)
, (C.Jan, C.Feb)
, (C.Jan, C.Feb, C.Mar) )
ORDER BY C.Jan desc , C.Feb desc , C.Mar desc
;
-----------------------------------------------------------------------------

JAN FEB MAR COUNTS
----------- ----------- ----------- -----------
1 - - 6
1 1 - 4
1 1 1 3
1 1 0 1
1 0 - 2
1 0 1 2
1 0 0 0

7 record(s) selected.

More general sample(for less than 12 layers):
------------------------------ Command Entered
------------------------------
WITH C (seq, m1 , m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12) AS (
VALUES ( 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
UNION ALL
SELECT seq + 1
, CASE WHEN MOD(seq , 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 2, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 4, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 8, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 16, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 32, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 64, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 128, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 256, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 512, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/1024, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/2048, 2) = 1 THEN 1 ELSE 0 END
FROM C
WHERE seq < 8
-- Adjust to 2 to the power of layer.
)
SELECT m1 , m2 , m3
-- , m4 , m5 , m6
-- , m7 , m8 , m9
-- , m10 , m11 , m12
, COUNT(mem_no) Counts
FROM C
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON C.m1 = M.Jan
AND C.m2 = M.Feb
AND C.m3 = M.Mar
-- AND C.m4 = M.Apr
-- AND C.m5 = M.May
-- AND C.m6 = M.Jun
-- AND C.m7 = M.Jul
-- AND C.m8 = M.Aug
-- AND C.m9 = M.Sep
-- AND C.m10 = M.Oct
-- AND C.m11 = M.Nov
-- AND C.m12 = M.Dec
WHERE C.m1 = 1
GROUP BY GROUPING SETS
(
(m1)
, (m1, m2)
, (m1, m2, m3)
-- , (m1, m2, m3, m4)
-- , (m1, m2, m3, m4, m5)
-- , (m1, m2, m3, m4, m5, m6)
-- , (m1, m2, m3, m4, m5, m6, m7)
-- , (m1, m2, m3, m4, m5, m6, m7, m8)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12)
)
ORDER BY
m1 desc , m2 desc , m3 desc
-- , m4 desc , m5 desc , m6 desc
-- , m7 desc , m8 desc , m9 desc
-- , m10 desc , m11 desc , m12 desc
;
-----------------------------------------------------------------------------

M1 M2 M3 COUNTS
----------- ----------- ----------- -----------
1 - - 6
1 1 - 4
1 1 1 3
1 1 0 1
1 0 - 2
1 0 1 2
1 0 0 0

7 record(s) selected.

[jane]

to*****@jp.ibm. com (Tokunaga T.) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

For less than 6 layers:
(The sample is for 3 layers. Modify ON conditions and add GROUP BY
columns and ORDER BY columns, for 4, 5 and 6 layers.)
------------------------------ Command Entered
------------------------------
SELECT C.Jan , C.Feb , C.Mar
, COUNT(mem_no) Counts
FROM (VALUES
(1,1,1,1,1,1), (1,1,1,1,1,0), (1,1,1,1,0,1),
(1,1,1,1,0,0)
, (1,1,1,0,1,1), (1,1,1,0,1,0), (1,1,1,0,0,1),
(1,1,1,0,0,0)
, (1,1,0,1,1,1), (1,1,0,1,1,0), (1,1,0,1,0,1),
(1,1,0,1,0,0)
, (1,1,0,0,1,1), (1,1,0,0,1,0), (1,1,0,0,0,1),
(1,1,0,0,0,0)
, (1,0,1,1,1,1), (1,0,1,1,1,0), (1,0,1,1,0,1),
(1,0,1,1,0,0)
, (1,0,1,0,1,1), (1,0,1,0,1,0), (1,0,1,0,0,1),
(1,0,1,0,0,0)
, (1,0,0,1,1,1), (1,0,0,1,1,0), (1,0,0,1,0,1),
(1,0,0,1,0,0)
, (1,0,0,0,1,1), (1,0,0,0,1,0), (1,0,0,0,0,1),
(1,0,0,0,0,0)
) AS C (Jan, Feb, Mar, Apr, May, Jun)
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON C.Jan = M.Jan
AND C.Feb = M.Feb
AND C.Mar = M.Mar
AND C.Apr = 0
AND C.May = 0
AND C.Jun = 0
GROUP BY GROUPING SETS ( (C.Jan)
, (C.Jan, C.Feb)
, (C.Jan, C.Feb, C.Mar) )
ORDER BY C.Jan desc , C.Feb desc , C.Mar desc
;
-----------------------------------------------------------------------------

JAN FEB MAR COUNTS
----------- ----------- ----------- -----------
1 - - 6
1 1 - 4
1 1 1 3
1 1 0 1
1 0 - 2
1 0 1 2
1 0 0 0

7 record(s) selected.

More general sample(for less than 12 layers):
------------------------------ Command Entered
------------------------------
WITH C (seq, m1 , m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12) AS (
VALUES ( 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
UNION ALL
SELECT seq + 1
, CASE WHEN MOD(seq , 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 2, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 4, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 8, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 16, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 32, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 64, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 128, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 256, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 512, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/1024, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/2048, 2) = 1 THEN 1 ELSE 0 END
FROM C
WHERE seq < 8
-- Adjust to 2 to the power of layer.
)
SELECT m1 , m2 , m3
-- , m4 , m5 , m6
-- , m7 , m8 , m9
-- , m10 , m11 , m12
, COUNT(mem_no) Counts
FROM C
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON C.m1 = M.Jan
AND C.m2 = M.Feb
AND C.m3 = M.Mar
-- AND C.m4 = M.Apr
-- AND C.m5 = M.May
-- AND C.m6 = M.Jun
-- AND C.m7 = M.Jul
-- AND C.m8 = M.Aug
-- AND C.m9 = M.Sep
-- AND C.m10 = M.Oct
-- AND C.m11 = M.Nov
-- AND C.m12 = M.Dec
WHERE C.m1 = 1
GROUP BY GROUPING SETS
(
(m1)
, (m1, m2)
, (m1, m2, m3)
-- , (m1, m2, m3, m4)
-- , (m1, m2, m3, m4, m5)
-- , (m1, m2, m3, m4, m5, m6)
-- , (m1, m2, m3, m4, m5, m6, m7)
-- , (m1, m2, m3, m4, m5, m6, m7, m8)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11)
-- , (m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12)
)
ORDER BY
m1 desc , m2 desc , m3 desc
-- , m4 desc , m5 desc , m6 desc
-- , m7 desc , m8 desc , m9 desc
-- , m10 desc , m11 desc , m12 desc
;
-----------------------------------------------------------------------------

M1 M2 M3 COUNTS
----------- ----------- ----------- -----------
1 - - 6
1 1 - 4
1 1 1 3
1 1 0 1
1 0 - 2
1 0 1 2
1 0 0 0

7 record(s) selected.

Thanks for your very good suggestion.

But I still prefer the your first post query output(it's exactly
format I want). It is rather clear for our business guys to fill in
the data into their diagram.

a little suggestion, can we use the rollup instead of grouping set?

Also I used following query to get each layer data like following

select Jan,count(mem_n o)
from MemberActiveSta tus
group by Jan desc
JAN 2
----------- -----------
1 6
select Jan, Feb,count(mem_n o)
from MemberActiveSta tus
group by Jan desc, Feb desc.

JAN FEB 3
----------- ----------- -----------
1 1 4
1 0 2
select Jan, Feb, Mar,count(mem_n o)
from MemberActiveSta tus
group by Jan desc, Feb desc, Mar desc.
JAN FEB MAR 4
----------- ----------- ----------- -----------
1 1 1 3
1 1 0 1
1 0 1 2
But I just think how I could put the last output column of each query
together
like following output format:

6
4 2
3 1 2 0

I could put the last output column of each query into one table , then
query from this table. But is there any simple way?

[Tokunaga T.]

> But I still prefer the your first post query output(it's exactly

format I want). It is rather clear for our business guys to fill in
the data into their diagram.

a little suggestion, can we use the rollup instead of grouping set? The reason I used GROUPING SETS in previous example is if I used
ROLLUP, the query result will include additional row corresponding to
GROUPING SETS ().
Also I used following query to get each layer data like following The reason I JOINed with table expression C is to include rows
corresponding to all combination of values of Jan, Feb and Mar, even
if counts of mem_no is 0.
select Jan,count(mem_n o)
from MemberActiveSta tus
group by Jan desc
JAN 2
----------- -----------
1 6
select Jan, Feb,count(mem_n o)
from MemberActiveSta tus
group by Jan desc, Feb desc.

JAN FEB 3
----------- ----------- -----------
1 1 4
1 0 2
select Jan, Feb, Mar,count(mem_n o)
from MemberActiveSta tus
group by Jan desc, Feb desc, Mar desc.
JAN FEB MAR 4
----------- ----------- ----------- -----------
1 1 1 3
1 1 0 1
1 0 1 2
By JOINning with table expression C, following additional row will be
included.
1 0 0 0
But I just think how I could put the last output column of each query
together
like following output format:

6
4 2
3 1 2 0

I could put the last output column of each query into one table , then
query from this table. But is there any simple way?

I afraid you think following example is too complex. But at least, it
will work.
Note:
You need only rows Jan = 1. So I removed m1 from table expression C.
Then I could use ROLLUP.
------------------------------ Command Entered
------------------------------
WITH
C (seq, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12) AS (
VALUES ( 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
UNION ALL
SELECT seq + 1
, CASE WHEN MOD(seq , 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 2, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 4, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 8, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 16, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 32, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 64, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 128, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 256, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/ 512, 2) = 1 THEN 1 ELSE 0 END
, CASE WHEN MOD(seq/1024, 2) = 1 THEN 1 ELSE 0 END
FROM C
WHERE seq < 4
-- Adjust to 2 to the power of (layer-1).
)
,
M AS (
SELECT m2 , m3
-- , m4 , m5
-- , m6 , m7 , m8 , m9 , m10 , m11 , m12
, COUNT(mem_no) Counts
, GROUPING(m2)
+GROUPING(m3)
-- +GROUPING(m4)
-- +GROUPING(m5)
-- +GROUPING(m6)
-- +GROUPING(m7)
-- +GROUPING(m8)
-- +GROUPING(m9)
-- +GROUPING(m10)
-- +GROUPING(m11)
-- +GROUPING(m12)
AS layer
FROM C
LEFT OUTER JOIN
MemberActiveSta tus AS M
ON M.Jan = 1
AND C.m2 = M.Feb
AND C.m3 = M.Mar
-- AND C.m4 = M.Apr
-- AND C.m5 = M.May
-- AND C.m6 = M.Jun
-- AND C.m7 = M.Jul
-- AND C.m8 = M.Aug
-- AND C.m9 = M.Sep
-- AND C.m10 = M.Oct
-- AND C.m11 = M.Nov
-- AND C.m12 = M.Dec
GROUP BY ROLLUP
(m2 , m3
-- , m4 , m5
-- , m6 , m7 , m8 , m9 , m10 , m11 , m12
)
)
,
Ord AS (
SELECT M.*
, ROWNUMBER()
OVER(PARTITION BY layer
ORDER BY m2 desc , m3 desc
-- , m4 desc , m5 desc
-- , m6 desc , m7 desc , m8 desc , m9 desc , m10
desc , m11 desc , m12 desc
) AS rnum
FROM M
)
,
Cat (rnum , layer , cat_counts) AS (
SELECT 1 , layer
, CAST( REPEAT(' ', 6*POWER(2,layer ) ) || CHAR(SMALLINT(c ounts))
AS VARCHAR(48) )
FROM Ord
WHERE rnum = 1
UNION ALL
SELECT Pre.rnum + 1 , Pre.layer
, cat_counts || REPEAT(' ', 6*(POWER(2,Pre. layer+1)-1) ) ||
CHAR(SMALLINT(c ounts))
FROM Cat Pre
, Ord New
WHERE Pre.rnum < 2048
AND Pre.layer = New.layer
AND New.rnum = Pre.rnum + 1
)

SELECT (SELECT MAX(layer) + 1 FROM Cat) - layer AS month
, cat_counts
FROM Cat R
WHERE rnum = (SELECT MAX(rnum)
FROM Cat S
WHERE R.layer = S.layer
)
;
-----------------------------------------------------------------------------

MONTH CAT_COUNTS
----------- ------------------------------------------------
1 6
2 4 2
3 3 1 2 0

3 record(s) selected.