[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - Sum on a varchar column

Posted on 2011-10-27
3
Medium Priority
?
334 Views
Last Modified: 2012-05-12
I need to make this SP run by getting a sum value on data1 which is a nvarchar column.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Reports_RunTimeByOperator]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Reports_RunTimeByOperator];
GO
CREATE PROCEDURE [dbo].[Reports_RunTimeByOperator]
(  
            @Operator As varchar(200),
            @NumberOfDays As integer  
)    
AS  
BEGIN  
   
DECLARE @DayN As INT  
   
DECLARE @sqlcmd varchar(4000), @cnt int  
   
SET @cnt = 0  
SET @sqlcmd = 'SELECT '  
   
WHILE @cnt < @NumberOfDays  
BEGIN  
  SET @DayN = (SELECT SUM([Data1]) FROM RemoteData WHERE [Operator] = @Operator AND [EventCode] = 54  
                  AND [EventDateTime] BETWEEN DATEADD(DAY, -@cnt-1, GetDate()) AND DATEADD(DAY, -@cnt, GetDate()))  
   
  SET @sqlcmd = @sqlcmd + '(' + LTRIM(STR(ISNULL(@DayN,0))) + '/60) As [' +    
      CONVERT(char(8), DATEADD(day, -@cnt, GETDATE()), 1) + '], '  
  SET @cnt = @cnt + 1  
END  
   
SET @sqlcmd = LEFT(@sqlcmd, LEN(@sqlcmd)-1)  
   
EXECUTE(@sqlcmd)  
END
GO
0
Comment
Question by:MBoy
3 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 2000 total points
ID: 37040019
SUM(CASE WHEN ISNUMERIC(Data1) = 1 THEN CAST(Data1 AS INT) ELSE 0 END)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37040063
please post what sqlcmd actually looks like...?

Print(@sqlcmd)

what error/problem are you actually getting?

is it an integer divide problem change /60 to /60.000?

or did you intend to have quotes around the subquery  at set @dayn = (select...?
0
 
LVL 3

Author Closing Comment

by:MBoy
ID: 37040158
Thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question