October 5, 2006 at 7:59 am
Hi all,
I dont know if this is the right forum to be posting this in, but I have a problem where I am working out a lot of sums and averages in a stored proc. The SQL code used to be in an ASP page, but there was one set of data which exceeded the allowed number of calls to tables.
To make a long story short (or longer), the client that I am doing this for has a set of data in a CSV file that was created a while ago. For most of the fields being pulled out of the database and averaged and summed is identical to the original data set. There are a couple of fields which are out of sync with the data in the original. The code is identical where these fields are coming from because some of the fields do match up.
I have been beating my head against a brick wall the last couple of days and I decided to come and ask some of my peers if they can see whats going wrong where.
I think that it might be when I am converting the two summed and counted fields and then dividing them. The code below is the portion of the stored proc that is causing the problems.
------------------------- code below ----------------------------------
CREATE procedure sp_GetDataforCSVContent (
@field varchar(20), -- select @field = 'position_level'
@Survey int, -- select @Survey = 11
@Reviewee int = NULL, -- select @Reviewee = 7654
@subReviewee int = NULL,
@gender int = NULL
) AS
set NOCOUNT on
declare @nProjectQuestion int
declare @modSQL varchar(300)
declare @updateSQL varchar(300)
create table #tmpTable (
questionID int,
fieldName varchar(50),
cText varchar(256),
cGroup varchar(50),
)
select @modSQL = 'alter table #tmpTable add '+@field+'_count int;'
select @modSQL = @modSQL + 'alter table #tmpTable add '+@field+'_sum int;'
select @modSQL = @modSQL + 'alter table #tmpTable add AgreeAvg FLOAT;'
select @modSQL = @modSQL + 'alter table #tmpTable add Imp_Avg FLOAT;'
select @modSQL = @modSQL + 'alter table #tmpTable add Dep_Agree_Avg FLOAT;'
select @modSQL = @modSQL + 'alter table #tmpTable add Dep_Imp_Avg FLOAT;'
exec (@modSQL)
declare @SQLString varchar(4000)
select @SQLString = 'insert into #tmpTable (questionID, fieldName, cText, cGroup, '+@field+'_Count, '+@field+'_Sum, AgreeAvg, Imp_Avg, Dep_Agree_Avg, Dep_Imp_Avg)
SELECT tDQA.questionID, tDQA.fieldName, tDQA.cText, tDQA.cGroup, COUNT(tP.'+@field+') AS '+@field+'_count,
(SELECT COUNT('+@field+') AS '+@field+'_count FROM tblParticipant tP WHERE tP.nSurvey='+cast(@survey as varchar(5))
if @reviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nReviewee = '+cast(@reviewee as varchar(6))
if @subreviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nRaterCategory = '+cast(@subReviewee as varchar(6))
if @gender is not NULL
select @SQLString = @SQLString + ' AND tP.gender = '+cast(@gender as varchar(6))
select @SQLString = @SQLString + ' AND tP.bSurveyCompleted=1 AND tP.'+@field+' <> ''''
AND tP.'+@field+' <> 0) AS '+@field+'_sum,
(SELECT AVG(CONVERT(FLOAT,tS.nAnswer)) FROM tblSurvey tS INNER JOIN tblParticipant tP ON tS.nParticipant = tP.nParticipant WHERE (tP.'+@field+' = tDQA.questionID)'
if @reviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nReviewee = '+cast(@reviewee as varchar(6))
if @subreviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nRaterCategory = '+cast(@subReviewee as varchar(6))
if @gender is not NULL
select @SQLString = @SQLString + ' AND tP.gender = '+cast(@gender as varchar(6))
select @SQLString = @SQLString + ' AND tP.bSurveyCompleted=1
AND (tS.nAnswer <> '''') AND (tS.nAnswer <> 0)) AS Agree_Avg,
(SELECT AVG(CONVERT(FLOAT,tS.nImportAnswer)) FROM tblSurvey tS
INNER JOIN tblParticipant tP ON tS.nParticipant = tP.nParticipant
WHERE (tP.'+@field+' = tDQA.questionID)'
if @reviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nReviewee = '+cast(@reviewee as varchar(6))
if @subreviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nRaterCategory = '+cast(@subReviewee as varchar(6))
if @gender is not NULL
select @SQLString = @SQLString + ' AND tP.gender = '+cast(@gender as varchar(6))
select @SQLString = @SQLString + ' AND tP.bSurveyCompleted=1 AND (tS.nImportAnswer <> '''') AND (tS.nImportAnswer <> 0)) AS Imp_Avg, (SELECT AVG(CONVERT(FLOAT,tS.nAnswer)) FROM tblSurvey tS INNER JOIN tblParticipant tP ON tS.nParticipant = tP.nParticipant
WHERE (tP.'+@field+' = tDQA.questionID) AND tP.bSurveyCompleted=1 AND (tS.nAnswer <> '''') AND (tS.nAnswer <> 0)) AS Dep_Agree_Avg,
(SELECT AVG(CONVERT(FLOAT,tS.nImportAnswer)) FROM tblSurvey tS INNER JOIN tblParticipant tP ON tS.nParticipant = tP.nParticipant WHERE (tP.'+@field+' = tDQA.questionID)
AND tP.bSurveyCompleted=1 AND (tS.nImportAnswer <> '''') AND (tS.nImportAnswer <> 0)) AS Dep_Imp_Avg
FROM tblParticipant tP INNER JOIN tblDemographicQAnswers tDQA ON tP.'+@field+' = tDQA.questionID WHERE tP.nSurvey = '+cast(@survey as varchar(5))
if @reviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nReviewee = '+cast(@reviewee as varchar(6))
if @subreviewee is not NULL
select @SQLString = @SQLString + ' AND tP.nRaterCategory = '+cast(@subReviewee as varchar(6))
if @gender is not NULL
select @SQLString = @SQLString + ' AND tP.gender = '+cast(@gender as varchar(6))
select @SQLString = @SQLString + ' AND tP.bSurveyCompleted=1 AND tP.'+@field+' <> '''' AND tP.'+@field+' <> 0 GROUP BY tDQA.fieldName, tDQA.questionID, tDQA.cText, tDQA.cGroup
ORDER BY tDQA.fieldName, tDQA.questionID, tDQA.cText'
------------------------------- Code above ---------------------------------
All through the stored proc, I am creating dynamic columns and in 2 cases, the column names are actually the value passed into the proc by the @field variable. This is because the size of the temp table is dependant on the number of questions to be averaged, there can be anything from 100-200 columns created. The questions are also based on which particular field is being summed up at each calling of the proc.
I think that there is something happening when I average and convert the highlighted areas that is either allowing a higher precision level to the float fields when I am converting the tS.nAnswer fields to floats.
Because the degree of inaccuracy is between 0.01 and 0.09 and the client wants the data to be exactly the same as the old data (I have a couple of spreadsheets if people want to inspect the resultant output).
If you could help me out, I would be extremely grateful.
Regards,
Mark
October 6, 2006 at 6:00 am
What are the ranges of possible values, and how many decimal places are required? You might be better off using the DECIMAL data type instead of float, which can lead to rounding and precision errors, particularly when repeated operations occur on the values.
Note the simple example below, which should return 1 as the result.
DECLARE @f1 float
, @f2 float
SET @f1 = 1.0
SET @f2 = 2.2
SELECT (2.2 * (1.0 /2.2 )) AS FloatNumber
DECLARE @n1 decimal(15, 5)
, @n2 decimal(15, 5)
SET @n1 = 1.0
SET @n2 = 2.2
SELECT ( @n2 * (@n1 / @n2) ) AS DecNumber
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply