473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

one query question

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.
Nov 12 '05 #1
7 2030
es**********@yahoo.com (jane) wrote in message news:<db**************************@posting.google. 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.
Nov 12 '05 #2
> 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(SUM( Jan )))
|| ' ' || CHAR(SMALLINT(SUM( 1-Jan )))
FROM MemberActiveStatus
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(SUM( Jan * Feb )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) )))
FROM MemberActiveStatus
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(SUM( Jan * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) * (1-Mar) )))
FROM MemberActiveStatus
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Diagram)
;
-----------------------------------------------------------------------------

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.
Nov 12 '05 #3
to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. 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(SUM( Jan )))
|| ' ' || CHAR(SMALLINT(SUM( 1-Jan )))
FROM MemberActiveStatus
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(SUM( Jan * Feb )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) )))
FROM MemberActiveStatus
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(SUM( Jan * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * Feb * (1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) * Mar )))
|| ' ' || CHAR(SMALLINT(SUM( (1-Jan) * (1-Feb) * (1-Mar) )))
FROM MemberActiveStatus
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Diagram)
;
-----------------------------------------------------------------------------

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(SUM( Jan
)))
FROM test_e
UNION ALL
SELECT 'Feb' , ' ' || CHAR(SMALLINT(SUM( Jan * Feb
)))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb)
)))
FROM test_e
UNION ALL
SELECT 'Mar' , CHAR(SMALLINT(SUM( Jan * Feb * Mar
)))
|| ' ' || CHAR(SMALLINT(SUM( Jan * Feb *
(1-Mar) )))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) * Mar
)))
|| ' ' || CHAR(SMALLINT(SUM( Jan * (1-Feb) *
(1-Mar) )))
FROM test_e
) X( Month_Name , Layer_Diagram)
ORDER BY LENGTH(Layer_Diagram)
;

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.
Nov 12 '05 #4
> 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
MemberActiveStatus 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.
Nov 12 '05 #5
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
MemberActiveStatus 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
MemberActiveStatus 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.
Nov 12 '05 #6
to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. 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
MemberActiveStatus 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
MemberActiveStatus 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_no)
from MemberActiveStatus
group by Jan desc
JAN 2
----------- -----------
1 6
select Jan, Feb,count(mem_no)
from MemberActiveStatus
group by Jan desc, Feb desc.

JAN FEB 3
----------- ----------- -----------
1 1 4
1 0 2
select Jan, Feb, Mar,count(mem_no)
from MemberActiveStatus
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?
Nov 12 '05 #7
> 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_no)
from MemberActiveStatus
group by Jan desc
JAN 2
----------- -----------
1 6
select Jan, Feb,count(mem_no)
from MemberActiveStatus
group by Jan desc, Feb desc.

JAN FEB 3
----------- ----------- -----------
1 1 4
1 0 2
select Jan, Feb, Mar,count(mem_no)
from MemberActiveStatus
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
MemberActiveStatus 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(counts))
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(counts))
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.
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.