Aggregate help - 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)

Aggregate help

Author

Topic

gwstudent
Starting Member

2 Posts

Posted - 2007-12-07 : 12:17:19

Can anyone help with this. I have a sp that is taking values and putting them into a temp table. One of the values I am getting from another table is secs (stored as float) and I would like to get the average for this. Here is part of my stored proc.

When I try to get the average for nTotalTime I get the following error: Error 403: Invalid operator for data type. Operator equals module, type equals float

CREATE TABLE #GroupProgress (
CourseUsers int,
JobOrgUsers int,
CourseTrainingHours decimal(6,2),
JOB_Title varChar(2000),
COURSE_NAME varchar(255),
ORG_Name varChar(255),
CompletionRate decimal(6,2),
AvgTime decimal,
TotalTraining decimal(6,2),
AvgScore decimal
)

Declare @g_courseIdent varchar(50), @g_courseName varchar(255), @g_courseType varchar(20), @g_jobTitle varchar(2000), @g_orgID varchar(50)
Declare @nTrainingHours decimal(6,2), @nJobOrgUsers int, @nCourseUsers int, @nJob_Title varChar(2000), @nOrg_Name varChar(255), @nTotalTime float, @nTotalScore float, @nTotalUsers varchar(50)

Declare @nCompletionRate float, @nTotalTraining float, @nAverageTime float, @nAverageScore float

DECLARE Get_GroupedData CURSOR FAST_FORWARD
FOR SELECT Distinct COURSE_ID, COURSE_NAME, JOB_TITLE, ORG_IDENT, COURSE_TYPE FROM #progressReport

-- Add cursor here to loop over all courses

OPEN Get_GroupedData
FETCH NEXT FROM Get_GroupedData
INTO @g_courseIdent, @g_courseName, @g_jobTitle, @g_orgID, @g_courseType

WHILE @@FETCH_STATUS = 0
BEGIN
IF @g_courseType = 'CURRICULUM'
BEGIN
Print 'Curriculum'
END
ELSE
BEGIN
SELECT @nTrainingHours = Training_Hours From CRS where CRS_Ident = @g_courseIdent
END

SELECT @nJobOrgUsers=Count(*) FROM Users
WHERE USERS_TITLE=@g_jobTitle AND USERS_CUSTOMERORG_IDENT=@g_orgID

SELECT @nCourseUsers=Count(*) FROM #progressReport
WHERE COURSE_ID=@g_courseIdent AND JOB_TITLE=@g_jobTitle AND ORG_IDENT=@g_orgID

--SELECT @nTotalTime=Total_TimeInSecs FROM SCO_DATA
--WHERE CRS_Ident=@g_courseIdent

SELECT @nTotalTime=Total_TimeInSecs/360
+ ':' + RIGHT('0'+RTRIM((cast(Total_TimeInSecs as int) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((Total_TimeInSecs % 3600) % 60),2)
FROM SCO_DATA
WHERE CRS_Ident=@g_courseIdent

SELECT @nTotalScore=AVG(Score_Raw ) FROM SCO_DATA
WHERE CRS_Ident=@g_courseIdent

SELECT @nTotalUsers=Count(CRS_Ident) FROM SCO_DATA
WHERE CRS_Ident=@g_courseIdent

-- Get the job title
Select @nJob_Title=Name From IDP_Position Where ID=@g_jobTitle

-- Get the Org name
Select @nOrg_Name=CustomerOrg_Name From CustomerOrg Where CustomerOrg_Ident=@g_orgID

-- Completion Rate: Number of Users with Complete / Total number of users within the Org.
Set @nCompletionRate = ((@nCourseUsers * 1.0) / (@nJobOrgUsers * 1.0)) * 100.0

-- Total Training Hours: Total Users with Complete * Seat hours entered by course admin (see any course form)
Set @nTotalTraining = @nCourseUsers * @nTrainingHours * 1.0

-- Average Time: (SCORM Only) Total time for each user / total number of users
Set @nAverageTime = @nTotalTime

--Average Score: Total Scores for a course / Total number of users
Set @nAverageScore = @nTotalScore / @nCourseUsers

INSERT INTO #GroupProgress Values(@nCourseUsers, @nJobOrgUsers, @nTrainingHours, @nJob_Title, @g_courseName, @nOrg_Name, @nCompletionRate, @nAverageTime, @nTotalTraining, @nAverageScore)
FETCH NEXT FROM Get_GroupedData
INTO @g_courseIdent, @g_courseName, @g_jobTitle, @g_orgID, @g_courseType

END

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 10:13:16

if you want help, you need to provide:

1. DDL for all tables involved. you have many tables referenced in this cursor but you only provided DDL for #GroupProgress.

2. sample data for all tables, in form of INSERT statements.

3. expected results

elsasoft.org

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-08 : 14:51:19

It's because you're trying to divide a float by an integer...

Daata type mismatch

George
<3Engaged!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 22:21:02

there's no issue dividing a float by an int.

the int will be implicitly converted and you'll get a float back. unless of course your int is zero...

declare @f float
declare @i int
set @f=1234.5678
set @i=10

select @f/@i --123.45678

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 18:36:44

yikes. sorry, i don't have time to wade through all that.

I did notice though, that you didn't provide anything I asked for above.

1. you didn't provide DDL for the tables involved.
2. you didn't provide sample data in the form of insert statements.
3. you didn't provide expected output based on the sample data.

also, learn how to use the [ code ] tag. it will preserve indenting and use a fixed width font so your code is not impossible to read. like this:


select 
   aaaa
  ,ssss
  ,dddd
  ,ffff
from UselessTable
where aaaa='I daresay, is not this code more readable?'

EDIT: just to put this post in context, the OP had a post just above this one with about 8000 lines of unformatted code, which they later deleted!

elsasoft.org

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-12-10 : 09:06:35

I'm guessing that your actual error message was "Operator equals modulo", not "Operator equals module", and that your problem is here:

 SELECT @nTotalTime=Total_TimeInSecs/360
+ ':' + RIGHT('0'+RTRIM((cast(Total_TimeInSecs as int) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((Total_TimeInSecs % 3600) % 60),2)
FROM SCO_DATA
WHERE CRS_Ident=@g_courseIdent

In the second function, you omit casting TimeInSecs as int prior to using the modulo operator.

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 10:09:35

Also =Total_TimeInSecs/360" should be "=CAST(Total_TimeInSecs/3600 AS VARCHAR)"...

Or use this simpler method if all total times never exceeds 24 hours.

SELECT	@nTotalTime =	CONVERT(VARCHAR, DATEADD(SECOND, Total_TimeInSecs, '19000101'), 108)
FROM			SCO_DATA
WHERE			CRS_Ident = @g_courseIdent

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources