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. 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.
> 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. 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.
> 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.
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. 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?
> 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |