Multiple groupings and unique

[tachtenberg]

I need to create a report that displays the number of unique sponsors grouped by how old the sponsorship is. (Lless than 1 year, 1 to 2 yars and over 2 years) Within that grouping I need to break it down by how often they pay (monthly, quarterly, semi annual or annual). Within these groups I need to break out what type of payment (Credit card, EFT, Payroll ded or other) If a sponsor has multiple sponsorships I need to count and catagorize only the oldest.

The following query returns the correct total number of rows but I have not been able to get it to work in CR8.5

SELECT DISTINCT
    plgf1.pledge_code, plgf1.id_code, plgf1.type_of_pledge, plgf1.stop_date, plgf1.paid_cnt, plgf1.post_status, plgf1.orig_start_date, plgf1.orig_motiv, plgf1.payment_type
FROM
    plgf plgf1
WHERE
    plgf1.paid_cnt > 0 AND
    plgf1.orig_start_date <= {d '2003-04-17'} AND
    (plgf1.post_status = 'A' OR
    plgf1.post_status = 'C' AND
    plgf1.stop_date >= {d '2002-10-01'}) AND
    plgf1.pledge_code >= 50900 AND
    plgf1.pledge_code <= 50999
group by plgf1.id_code
order by plgf1.orig_motiv

 

[Ngolem]

so you create appropriate groups in Crystal

Group 1 based on the age of the sponsor
Group 2 based on payment plan
Group 3 based on payment type

What is the problem?

Jim Broadbent