Re: subqueries comparision

LinkBack

Thread Tools

Display Modes

#1 (permalink)

02-28-2008, 05:00 PM

Steve Kass

Posts: n/a

Re: subqueries comparision

Vincent,

Maybe something like this?

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 your_date_column >= '20020801' AND your_date_column <
'20020901'
THEN 1 ELSE 0 END) as 'aug_02_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%'
) AS T

Steve Kass
Drew University

Vincent Jones wrote:

>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%
>
>

« Disadvantages of the implementation of explicit subtypes on MS SQL 7 and Oracle8i/9i | Re: Limiting precision when using a formula for field »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Forum Jump

All times are GMT. The time now is 04:30 AM.

SEO

Re: subqueries comparision