SQL Case and Grouping by Date

From: ct witter <norcold1_at_yahoo.com>
Date: 23 Jan 2006 11:21:33 -0800
Message-ID: <1138044093.706599.202200@z14g2000cwz.googlegroups.com>

I have the following query:

SELECT
G.GROUP_ID,
'1/2/2006' CONTRACT_START,

M.SEQ_MEMB_ID,
M.SEQ_SUBS_ID,
M.MEMBER_ID,

CBA.RULE_ID,
 CASE

 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('D','Q') THEN 'QTY'
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('W') THEN 'WEIGHTED'
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 NOT IN('W','D','Q')

THEN 'AMT'
 WHEN BR.RULE_TYPE<>'20' THEN 'AMT'
END AS "ACCUM_SOURCE",
SUM(CASE
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('D','Q') AND CBA.CLAIM_QTY='0' THEN 1
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('D','Q') THEN DECODE(CBA.CLAIM_QTY, NULL,1,CBA.CLAIM_QTY)  WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('W') THEN DECODE(CBA.WEIGHTED_QTY, NULL,0,CBA.WEIGHTED_QTY)  WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 NOT IN('W','D','Q') THEN DECODE(CBA.CLAIM_AMT, NULL,0,CBA.CLAIM_AMT)  WHEN BR.RULE_TYPE<>'20' THEN DECODE(CBA.CLAIM_AMT, NULL,0,CBA.CLAIM_AMT)
END) AS "ACCUM_VALUE"
FROM
DB.G_MASTER G,
DB.COST_BASE_ACCUM CBA,
DORIS.MEMBERS M,
DORIS.B_RULE BR
WHERE

CBA.SEQ_GROUP_ID = G.SEQ_GROUP_ID   AND
CBA.SEQ_MEMB_ID=M.SEQ_MEMB_ID   AND
CBA.RULE_ID = BR.RULE_ID AND

M.MEMBER_ID='9999999' AND
CBA.DETAIL_SVC_DATE BETWEEN TO_DATE('1/1/2005','MM/DD/YYYY') AND TO_DATE('12/31/2005','MM/DD/YYYY' )
GROUP BY

G.GROUP_ID,
M.SEQ_MEMB_ID,
M.SEQ_SUBS_ID,
M.MEMBER_ID,

CBA.RULE_ID,
 CASE

 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('D','Q') THEN 'QTY'
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 IN('W') THEN 'WEIGHTED'
 WHEN BR.RULE_TYPE='20' AND BR.ATTRIBUTE_CHAR_5 NOT IN('W','D','Q')

THEN 'AMT'
 WHEN BR.RULE_TYPE<>'20' THEN 'AMT'
END,
CBA.DETAIL_SVC_DATE Which yields the following results:

GROUP_ID|CONTRACT_START|SEQ_MEMB_ID|SEQ_SUBS_ID|MEMBER_ID|RULE_ID|ACCUM_SOURCE|ACCUM_VALUE
999000|1/2/2006|99999|99999|9999999|VA1|QTY|3
999000|1/2/2006|99999|99999|9999999|VA1|QTY|2
999000|1/2/2006|99999|99999|9999999|VA1|QTY|2
999000|1/2/2006|99999|99999|9999999|VA1|QTY|2
999000|1/2/2006|99999|99999|9999999|VA1|QTY|1
999000|1/2/2006|99999|99999|9999999|VA1|QTY|1

The problem that I am having is that I need to count the occurances not the total value, the results would look like:

GROUP_ID|CONTRACT_START|SEQ_MEMB_ID|SEQ_SUBS_ID|MEMBER_ID|RULE_ID|ACCUM_SOURCE|ACCUM_VALUE 999000|1/2/2006|99999|99999|9999999|VA1|QTY|6 This is only true for types BR.ATTRIBUTE_CHAR_5 ='D'

Any suggestions? Received on Mon Jan 23 2006 - 13:21:33 CST