SQL - Sum on a varchar column

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
LVL 3
MBoyAsked:
Who is Participating?
 
tim_csConnect With a Mentor Commented:
SUM(CASE WHEN ISNUMERIC(Data1) = 1 THEN CAST(Data1 AS INT) ELSE 0 END)
0
 
LowfatspreadCommented:
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
 
MBoyAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.