Hey guys, I have a question.
I have a table with 2 decimal fields 10,2.
In my sp i get those fields and divid them by an number.
I get like 5 or 6 0's trailing the calculation.
Ex: 191.6/2 I get 95.800000
Why?
Here is my sp:
CREATE PROCEDURE sp_SummaryReport
(
@.startdate datetime,
@.enddate datetime
)
AS
BEGIN
SELECT tblLegendReportAbv.ReportType AS ReportType,
SUM(tblSummaryData.Volume) AS Volume,
SUM(tblSummaryData.NetEffect)/COUNT(tblSummaryData.DataID) AS NetEffect,
SUM(tblSummaryData.GrossEffect)/COUNT(tblSummaryData.DataID) AS GrossEffect
FROM tblSummaryData
INNER JOIN tblLegendReportAbv ON LTRIM(RTRIM(LOWER(tblSummaryData.ReportType))) = LTRIM(RTRIM(LOWER(tblLegendReportAbv.ReportAbv)))
WHERE tblSummaryData.WeekEndDate BETWEEN @.startdate AND @.enddate
GROUP BY tblSummaryData.ReportType,tblLegendReportAbv.Repor tType
END
GO
Please helpThat's beacause the implicit conversion made by SQL Server when dividing to different types. For more info see "Data Type Precedence" in your SQL help file.
But this only an aesthetic problem, it can be easily solved with an explicit conversion or with a cast like this:
cast(191.6/2 as decimal(10,2))
For more info on cast and convert see "CAST and CONVERT" in your SQL help file.
Best regards!|||Thank you.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment