Convert varchar as in - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert varchar as in

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-17 : 14:55:35

Hello,

I am trying to get the sum of an column. I need the sum of Total_SR (COUNT(SR_AREA) AS Total_SR).

SELECT CASE WHEN INS_PRODUCT = 'Assist Line' THEN 'Assist_Line' ELSE CAST(INS_PRODUCT AS varchar(20)) END AS Split, Call_Type, COUNT(SR_AREA) AS Total_SR
FROM (SELECT CASE WHEN SR_AREA = 'No Access' OR
SR_AREA = 'Escalated Call' OR
SR_AREA = 'Copayments' OR
SR_AREA = 'KB/Scripts' OR
SR_AREA = 'TPJI' OR
SR_AREA = 'Eligibility' OR
SR_AREA = 'Mean Test' OR
SR_AREA = 'Refundl' OR
SR_AREA = 'Repayment Plan' OR
SR_AREA = 'Top?DMC Offset' OR
SR_AREA = 'Security' OR
SR_AREA = 'Waiver' OR
SR_AREA = 'Death of Veteran' OR
SR_AREA = 'Regional Office' THEN SR_AREA END AS Call_Type, SR_AREA, INS_PRODUCT, CREATED
FROM S_SRV_REQ AS S_SRV_REQ_1 WITH (NOLOCK)
WHERE (INS_PRODUCT = 'Assist Line')) AS a
WHERE (INS_PRODUCT IS NOT NULL) AND (CREATED >= @Startdate) AND (CREATED < DATEADD(d, 1, @Enddate)) AND (Call_Type IS NOT NULL)
GROUP BY INS_PRODUCT, Call_Type, SR_AREA
ORDER BY Total_SR DESC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-17 : 15:03:40

Is there an error?

You might want to remove SR_AREA from the GROUP BY list as you are doing a COUNT on it.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-17 : 15:09:12

Good catch, I meant to remove SR_AREA from the GROUP BY list. When I run my query I get some of the results I aiming for. Where I am having trouble is converting the 'Total_SR' into an int so I can get the SUM, which will allow me to get the percentage and and cumualative percent columns.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-17 : 15:14:04

I'm sorry...I dont get it..the return value of a count function IS an int. It shouldn't stop you from doing those calculations.

How exactly are you doing those calcs? Can you explain with some sample data?

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-17 : 15:32:42

This is what my data looks like.
Split Call_Type Total_SR

Assist_Line No Access 753
Assist_Line KB/Scripts 590
Assist_Line Escalated Call 510
Assist_Line Copayments 360
Assist_Line TPJI 158
Assist_Line Eligibility 95
Assist_Line Repayment Plan 42
Assist_Line Security 30
Assist_Line Death of Veteran 25
Assist_Line Waiver 23
Assist_Line Regional Office 11

I tried placing the SUM(Total_SR) in the SELECT but iget an error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:18:29

quote:
Originally posted by sergeant_time

This is what my data looks like.
Split Call_Type Total_SR

Assist_Line No Access 753
Assist_Line KB/Scripts 590
Assist_Line Escalated Call 510
Assist_Line Copayments 360
Assist_Line TPJI 158
Assist_Line Eligibility 95
Assist_Line Repayment Plan 42
Assist_Line Security 30
Assist_Line Death of Veteran 25
Assist_Line Waiver 23
Assist_Line Regional Office 11

I tried placing the SUM(Total_SR) in the SELECT but iget an error

why. as per your sample data its int so SUM() should work fine as already pointed out by vijayisonly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:23:15

Can you show your currently used query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-18 : 12:15:26

SELECT CASE WHEN INS_PRODUCT = 'Assist Line' THEN 'Assist_Line' ELSE CAST(INS_PRODUCT AS varchar(20)) END AS Split, Call_Type, COUNT(SR_AREA)
AS Total_SR
FROM (SELECT CASE WHEN SR_AREA = 'No Access' OR
SR_AREA = 'Escalated Call' OR
SR_AREA = 'Copayments' OR
SR_AREA = 'KB/Scripts' OR
SR_AREA = 'TPJI' OR
SR_AREA = 'Eligibility' OR
SR_AREA = 'Mean Test' OR
SR_AREA = 'Refundl' OR
SR_AREA = 'Repayment Plan' OR
SR_AREA = 'Top?DMC Offset' OR
SR_AREA = 'Security' OR
SR_AREA = 'Waiver' OR
SR_AREA = 'Death of Veteran' OR
SR_AREA = 'Regional Office' THEN SR_AREA END AS Call_Type, SR_AREA, INS_PRODUCT, CREATED
FROM S_SRV_REQ AS S_SRV_REQ_1 WITH (NOLOCK)
WHERE (INS_PRODUCT = 'Assist Line')) AS a
WHERE (INS_PRODUCT IS NOT NULL) AND (CREATED >= @Startdate) AND (CREATED < DATEADD(d, 1, @Enddate)) AND (Call_Type IS NOT NULL)
GROUP BY INS_PRODUCT, Call_Type
ORDER BY Total_SR DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:21:06

I cant spot anything problematic here. what was error you got?

Also just noticed you've used NOLOCK. Are you aware of its consequences?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-18 : 12:36:04

No should I take that out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:41:24

It can give you uncommited data (dirty reads). So if you're unsure about volatility of data then better not use it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-18 : 12:51:14

Thanks I tooked it out. I also made some changes to my query; I took out CASE WHEN INS_PRODUCT = 'Assist Line' THEN 'Assist_Line' ELSE CAST(INS_PRODUCT AS varchar(20)) END AS Split.
I insert CONVERT(numeric(10, 2), COUNT('Total_SR')) /
(SELECT CONVERT(numeric(10, 2), COUNT('Total_SR')) AS Expr1
FROM S_SRV_REQ AS S_SRV_REQ_1) * 100 AS [ Percent_of_Call_Type] in the SELECT clause. I was able to get data(which I will insert below), but I am not sure if its the right method. When I placed a SUM clause it tells me the 'Total_SR' column is varchar.

SELECT Call_Type, COUNT(SR_AREA) AS Total_SR, CONVERT(numeric(10, 2), COUNT('Total_SR')) /
(SELECT CONVERT(numeric(10, 2), COUNT('Total_SR')) AS Expr1
FROM S_SRV_REQ AS S_SRV_REQ_1) * 100 AS [ Percent_of_Call_Type]
FROM (SELECT CASE WHEN SR_AREA = 'No Access' OR
SR_AREA = 'Escalated Call' OR
SR_AREA = 'Copayments' OR
SR_AREA = 'KB/Scripts' OR
SR_AREA = 'TPJI' OR
SR_AREA = 'Eligibility' OR
SR_AREA = 'Mean Test' OR
SR_AREA = 'Refundl' OR
SR_AREA = 'Repayment Plan' OR
SR_AREA = 'TopDMC Offset' OR
SR_AREA = 'Security' OR
SR_AREA = 'Waiver' OR
SR_AREA = 'Death of Veteran' OR
SR_AREA = 'Regional Office' THEN SR_AREA END AS Call_Type, SR_AREA, INS_PRODUCT, CREATED
FROM S_SRV_REQ AS S_SRV_REQ
WHERE (INS_PRODUCT = 'Assist Line')) AS a
WHERE (INS_PRODUCT IS NOT NULL) AND (CREATED >= @Startdate) AND (CREATED < DATEADD(d, 1, @Enddate)) AND (Call_Type IS NOT NULL)
GROUP BY INS_PRODUCT, Call_Type
ORDER BY Total_SR DESC

MY results

CALL_TYPE Total_SR Percent_of_Call_type
No Access 753 0.006967816
KB/Scripts 590 0.005459511
Escalated Call 510 0.004719238
Copayments 360 0.003331227
TPJI 158 0.001462038
Eligibility 95 0.000879074
Repayment Plan 42 0.000388643
Security 30 0.000277602
Death of Veteran 25 0.000231335
Waiver 23 0.000212828
Regional Office 11 0.000101787

I am questioning the percent column( ex: it should be 11/(sum of Total_SR))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:56:11

COUNT('Total_SR') should be COUNT(Total_SR) no need of enclosure with quotes

also
CONVERT(numeric(10, 2), COUNT('Total_SR')) /
(SELECT CONVERT(numeric(10, 2), COUNT('Total_SR')) AS Expr1
FROM S_SRV_REQ AS S_SRV_REQ_1) * 100

should be COUNT(Total_SR)*100.0/(SELECT COUNT(Total_SR) AS Expr1
FROM S_SRV_REQ AS S_SRV_REQ_1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:56:52

also see reason for latter modification here

http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-18 : 14:25:54

Thanks, I get error ORDER BY CLAUSE

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 14:33:37

What error? Also post the whole query you tried.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-18 : 14:43:41

SELECT Call_Type, COUNT(SR_AREA) AS Total_SR, CONVERT(numeric(10, 2), COUNT(Total_SR)) / COUNT(Total_SR) * 100.0 /
(SELECT COUNT(Total_SR) AS Percent_Calltype
FROM S_SRV_REQ) AS b
FROM (SELECT CASE WHEN SR_AREA = 'No Access' OR
SR_AREA = 'Escalated Call' OR
SR_AREA = 'Copayments' OR
SR_AREA = 'KB/Scripts' OR
SR_AREA = 'TPJI' OR
SR_AREA = 'Eligibility' OR
SR_AREA = 'Mean Test' OR
SR_AREA = 'Refundl' OR
SR_AREA = 'Repayment Plan' OR
SR_AREA = 'TopDMC Offset' OR
SR_AREA = 'Security' OR
SR_AREA = 'Waiver' OR
SR_AREA = 'Death of Veteran' OR
SR_AREA = 'Regional Office' THEN SR_AREA END AS Call_Type, SR_AREA, INS_PRODUCT, CREATED
FROM S_SRV_REQ AS S_SRV_REQ_1 WITH (NOLOCK)
WHERE (INS_PRODUCT = 'Assist Line')) AS a
WHERE (INS_PRODUCT IS NOT NULL) AND (CREATED >= @Startdate) AND (CREATED < DATEADD(d, 1, @Enddate)) AND (Call_Type IS NOT NULL)
GROUP BY INS_PRODUCT, Call_Type, Total_SR
ORDER BY Total_SR DESC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 15:05:10

Call_type seems to be the same field as SR_AREA..why do you need two of them? Can you give this a try?

SELECT   a.Call_Type, 
         COUNT(a.SR_AREA) AS Total_SR,
		 COUNT(Total_SR)*100.0/(SELECT COUNT(Total_SR) AS Percent_Calltype FROM S_SRV_REQ)
FROM     (SELECT CASE 
                   WHEN SR_AREA = 'No Access' 
                         OR SR_AREA = 'Escalated Call' 
                         OR SR_AREA = 'Copayments' 
                         OR SR_AREA = 'KB/Scripts' 
                         OR SR_AREA = 'TPJI' 
                         OR SR_AREA = 'Eligibility' 
                         OR SR_AREA = 'Mean Test' 
                         OR SR_AREA = 'Refundl' 
                         OR SR_AREA = 'Repayment Plan' 
                         OR SR_AREA = 'TopDMC Offset' 
                         OR SR_AREA = 'Security' 
                         OR SR_AREA = 'Waiver' 
                         OR SR_AREA = 'Death of Veteran' 
                         OR SR_AREA = 'Regional Office' 
                   THEN SR_AREA 
				   else null
                 END AS Call_Type, 
                 SR_AREA, 
                 CREATED 
          FROM   S_SRV_REQ AS S_SRV_REQ_1
          WHERE  INS_PRODUCT = 'Assist Line') AS a 
WHERE CREATED between @Startdate and DATEADD(d,1,@Enddate) AND Call_Type IS NOT NULL 
GROUP BY a.Call_Type
ORDER BY a.Total_SR DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources