one query question

**jane** · Nov 12 '05, 05:51 AM

Re: one query question

esthershensh@ya hoo.com (jane) wrote in message news:<db914535. 0311131224.5da9 0904@posting.go ogle.com>...[color=blue]
> 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.[/color]

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.** · Nov 12 '05, 05:51 AM

Re: one query question

> So, the table is like[color=blue]
>
> 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
>[/color]
------------------------------ 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** · Nov 12 '05, 05:51 AM

Re: one query question

tonkuma@jp.ibm. com (Tokunaga T.) wrote in message news:<8156d9ae. 0311150146.7d59 e286@posting.go ogle.com>...[color=blue][color=green]
> > 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
> >[/color]
> ------------------------------ 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.[/color]

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.** · Nov 12 '05, 05:51 AM

Re: one query question

> Another questions is it is too much edit work if I want to have 6 or[color=blue]
> more layer. It is terrible to do the edit work and format the result.[/color]
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.** · Nov 12 '05, 05:51 AM

Re: one query question

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** · Nov 12 '05, 05:53 AM

Re: one query question

tonkuma@jp.ibm. com (Tokunaga T.) wrote in message news:<8156d9ae. 0311161421.2cbb 4165@posting.go ogle.com>...[color=blue]
> 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.[/color]

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.** · Nov 12 '05, 05:53 AM

Re: one query question

> But I still prefer the your first post query output(it's exactly[color=blue]
> 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?[/color]
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 ().[color=blue]
>
> Also I used following query to get each layer data like following[/color]
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.[color=blue]
>
> 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
>[/color]
By JOINning with table expression C, following additional row will be
included.
1 0 0 0[color=blue]
>
> 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?[/color]
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.

one query question

one query question

Comment

Comment

Comment

Comment

Comment

Comment

Comment