why won't my HAVING clause work with DECIMAL? 1

[1DMF]

Hi,

I have this query...

	-- Add the parameters for the stored procedure here
	@MemNo varchar(12) = '%', 
	@StartDate char(10) = NULL,
	@EndDate char(10) = NULL,
	@AvgPer decimal(4,2) = NULL,
	@Members varchar(12) = '%'
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Set default date
	Set @StartDate = coalesce(@StartDate, '2008-01-01') 
	Set @EndDate = coalesce(@EndDate, Convert(char(10), GetDate(), 121))
	Set @AvgPer = coalesce(@AvgPer, 0.0)

	
	CREATE TABLE #RAG (   
		ContactID int,
		RAG varchar(5),
		Val smallint,
		Num smallint,
		MemNo varchar(20)
    )

	INSERT INTO #RAG (ContactID,RAG,Val,Num,MemNo)   
	EXEC dbo.spRAG_Ratings

    -- Insert statements for procedure here
	SELECT c.ContactID,FirstName + ' ' + LastName AS Adviser, COUNT(ca.Case_ID) AS Cases, SUM(ch.Checks) AS Checks, cast((cast(SUM(ch.Checks) AS DECIMAL(4,2)) / cast(COUNT(ca.Case_ID) AS DECIMAL(4,2))) AS DECIMAL(4,2)) AS AvgPer,
	Case When
			(RAG IS NULL)
			Then 'N/A'
			Else RAG
			End
	AS RAG
				
	FROM Compliance_Audit ca
	LEFT OUTER JOIN (SELECT Case_ID, COUNT(Case_ID) AS Checks FROM Case_Checker_Grades GROUP BY Case_ID) ch
	ON ca.Case_ID = ch.Case_ID
	INNER JOIN Contacts c 
	ON ca.contactid = c.contactid
	LEFT OUTER JOIN #RAG r
	ON r.ContactID = c.ContactID
	WHERE (ca.[CDate Visited] BETWEEN @StartDate AND @EndDate) AND c.[membership number] LIKE @MemNo + '%' AND Rating > 0 AND c.ContactTypeID LIKE @Members + '%'
	GROUP BY c.ContactID, FirstName + ' ' + LastName,RAG, c.LastName 
	HAVING SUM(ch.Checks) / COUNT(ca.Case_ID)  >= @AvgPer
	ORDER BY c.LastName 
	
	DROP TABLE #RAG

END

but when @AvgPer is given a value of 1.2 , it must be rounding or something as the only result I get is those 2 or over.

Now I had to cast on the select to get the integer math to give me the required decimal, but when I use the same expression in the having clause, I get the following error...

Arithmetic overflow error converting int to data type numeric

How do I fix this?

Thanks,
1DMF




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

ch.Checks is from a derived table using the count function, so it is an integer. COUNT(ca.Case_id) also returns an integer.


Having int / int >= decimal

The integer division will occur before the comparison, so you end up with integer math on the left side.

To correct this....

 HAVING [!]1.0 * [/!]SUM(ch.Checks) / COUNT(ca.Case_ID)  >= @AvgPer

Multiplying by 1.0 forces the math calculations to be treated like decimals instead of integers.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

awesome George, thanks you so much.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads