CrossTab Queries Groupings

From: Vincent Jones <vncntj_at_hotmail.com>
Date: 16 Apr 2003 11:34:30 -0700
Message-ID: <ba2a2d30.0304161034.326ebc6c@posting.google.com>

I have this sp
Alter Procedure ps_data_bak
AS
Select Distinct

	CASE
		When Substring(o.Appeal_Code,4,1) = 'U' Then 'Membership -
Individual and Household'
		When Substring(o.Appeal_Code,4,1) = 'H' Then 'Membership -
Individual and Household'
		When Substring(o.Appeal_Code,4,1) = 'I' Then 'Membership -
Individual and Household'
		When Substring(o.Appeal_Code,4,1) = 'A' Then 'Membership -
Contributor and Supporter'
		When Substring(o.Appeal_Code,4,1) = 'P' Then 'Membership -
Contributor and Supporter'
		When Substring(o.Appeal_Code,4,1) = 'B' Then 'Membership -
Contributor and Supporter'
		When Substring(o.Appeal_Code,4,1) = 'C' Then 'Development -
Sustainer, Sponsor and Patron'
		When Substring(o.Appeal_Code,4,1) = 'D' Then 'Development -
Sustainer, Sponsor and Patron'
		When Substring(o.Appeal_Code,4,1) = 'E' Then 'Development -
Sustainer, Sponsor and Patron'
		When Substring(o.Appeal_Code,4,1) = 'F' Then 'Development Level 2'
	END AS Code,
	CASE
		When Substring(o.Appeal_Code,2,1) = 'D' Then 'Upgrade'
		When Substring(o.Appeal_Code,2,1) = 'R' Then 'Renewal'
		When Substring(o.Appeal_Code,2,1) = 'L' Then 'Lapse'
	End As Type,
	Sum(o.Call_Resolution)  'Call Resolution'

From tblLead As o
Group By Substring(o.Appeal_Code,4,1), Substring(o.Appeal_Code,2,1) Having Substring(o.Appeal_Code,2,1) Is Not Null Order By Code, Type

But I still get this output:

Code	Type	Call Resolution
Development - Sustainer, Sponsor and Patron	Lapse	6
Development - Sustainer, Sponsor and Patron	Lapse	23
Development - Sustainer, Sponsor and Patron	Lapse	187
Development - Sustainer, Sponsor and Patron	Renewal	83
Development - Sustainer, Sponsor and Patron	Renewal	289
Development - Sustainer, Sponsor and Patron	Renewal	1107
Development - Sustainer, Sponsor and Patron	Upgrade	22
Development - Sustainer, Sponsor and Patron	Upgrade	73
Development - Sustainer, Sponsor and Patron	Upgrade	265
Development Level 2	Lapse	12
Development Level 2	Renewal	44
Membership - Contributor and Supporter	Lapse	240
Membership - Contributor and Supporter	Lapse	558
Membership - Contributor and Supporter	Renewal	1439
Membership - Contributor and Supporter	Renewal	2647
Membership - Contributor and Supporter	Upgrade	590
Membership - Contributor and Supporter	Upgrade	1029
Membership - Individual and Household	Lapse	1311
Membership - Individual and Household	Lapse	2210
Membership - Individual and Household	Renewal	2669
Membership - Individual and Household	Renewal	5506
Membership - Individual and Household	Upgrade	4
Membership - Individual and Household	Upgrade	3570
Membership - Individual and Household	Upgrade	6291

I just want to see
Development - Sustainer, Sponsor and Patron	Lapse 216
Development - Sustainer, Sponsor and Patron	Renewal	1479

Thanks. Received on Wed Apr 16 2003 - 13:34:30 CDT