Hi ,

You must apply where clause eg: Where DateField Between DateRange1 AND
DateRange2 in derived table defination.
Refer my comments with ADD>> clause.

(SELECT
LEFT(appeal_code, 4) AS appeal_code,
CASE
WHEN LEFT(appeal_Code, 4) LIKE '_D%' THEN 'Upgrade'
WHEN LEFT(appeal_Code, 4) LIKE '_R%' THEN 'Renewal'
WHEN LEFT(appeal_Code, 4) LIKE '_L%' THEN 'Lapse' END
AS type,
CASE call_resolution WHEN 1 THEN COUNT(call_resolution)
ELSE '' END AS 'total_yes'
FROM tblLead_sheets
ADD>> Where Clause........
GROUP BY appeal_code, call_resolution,
If_Yes_Pledge_Or_Credit_Card)
AS tbl

Thanks Amit.

vncntj@hotmail.com (Vincent Jones) wrote in message news:<ba2a2d30.0306301036.7b32e334@posting.google. com>...
> this is my query
> SELECT
> appeal_code,
> type,
> SUM(total_yes) AS 'total_yes'
> FROM
> (SELECT
> LEFT(appeal_code, 4) AS appeal_code,
> CASE
> WHEN LEFT(appeal_Code, 4) LIKE '_D%' THEN 'Upgrade'
> WHEN LEFT(appeal_Code, 4) LIKE '_R%' THEN 'Renewal'
> WHEN LEFT(appeal_Code, 4) LIKE '_L%' THEN 'Lapse' END AS type,
> CASE call_resolution WHEN 1 THEN COUNT(call_resolution) ELSE '' END AS 'total_yes'
> FROM tblLead_sheets
> GROUP BY appeal_code, call_resolution, If_Yes_Pledge_Or_Credit_Card) AS tbl
> GROUP BY appeal_Code, type
> HAVING appeal_code LIKE 'T%'
>
> and my output:
>
> appeal_code type total_yes
> ----------- ------- -----------
> TL1D Lapse 5
> TD1I Upgrade 477
> TR5A Renewal 102
> TL1H Lapse 168
> TR3A Renewal 428
>
> This output is based on a date range. July 1, 2002 - May 31, 2003
> I need an output that states for one of the months:
>
> appeal_code type total_yes Aug 02 total_yes percentage
> ----------- ------- ----------------------------- ----------
> TL1D Lapse 5 1 20%
> TD1I Upgrade 477 35 7%
> TR5A Renewal 102 40 40%
> TL1H Lapse 168 79 47%
> TR3A Renewal 428 300 70%

you guys rock!!
thanks...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Vincent Jones (vncntj@hotmail.com) writes:
> one question
> how can you have..
>...
> and not have to put "aug_02_total_yes" in the group by section. usually
> when I do this it tells me that
>
> "invalid in the select list because it is not contained in either an
> aggregate function"

The answer is, because you don't have the column in any GROUP BY. Here is
a reformatted version of the query which shows what is going on:


SELECT appeal_code, type, total_yes, aug_02_total_yes,
str(cast(100 * cast(aug_02_total_yes as decimal(10,0)) /
total_yes as int)
) + '%' as percentage
FROM (SELECT appeal_code, type, SUM(total_yes) AS 'total_yes',
SUM (CASE WHEN Date_fld >= '20020801'
AND Date_fld < '20020901'
THEN total_yes
ELSE 0
END) as 'aug_02_total_yes'
FROM (SELECT LEFT(appeal_code, 4) AS appeal_code,
COUNT(call_resolution) AS 'total_resolves',
CASE WHEN LEFT(appeal_Code, 4) LIKE '_D%'
THEN 'Upgrade'
WHEN LEFT(appeal_Code, 4) LIKE '_R%'
THEN 'Renewal'
WHEN LEFT(appeal_Code, 4) LIKE '_L%'
THEN 'Lapse'
END AS type,
CASE call_resolution
WHEN 1 THEN COUNT(call_resolution)
ELSE ''
END AS 'total_yes',
date_fld
FROM tblLead_sheets
GROUP BY appeal_code, call_resolution, date_fld) AS tbl
GROUP BY appeal_Code, type
HAVING appeal_code LIKE 'T%') AS T

That is, the GROUP BY clause you are wondering about is part of
the outer of the two derived tables.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp