get number as decimal

[Albano]

I a Have this STP, it return de AVG, but return a round number, I whant to return the original decimal number round on 2 decimal places.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[STP_MEDIA_AVALIACAO]
(
@pVISCOD int
,@pCLICOD int
,@pCICCOD varchar(10)
,@pLOCCOD varchar(10)
)
AS
SET NOCOUNT ON
SELECT
AVG(CAVRESPOSTA) MEDIA
FROM VISITASCRITERIOSAVALIACAO
WHERE
VISCOD = (CASE WHEN @pVISCOD IS NULL THEN VISCOD else @pVISCOD end)
AND CICCOD LIKE (CASE WHEN @pCICCOD IS NULL THEN CICCOD else @pCICCOD end)
AND LOCCOD LIKE (CASE WHEN @pLOCCOD IS NULL THEN LOCCOD else @pLOCCOD end)
AND CLICOD = (CASE WHEN @pCLICOD IS NULL THEN CLICOD else @pCLICOD end)



thanks

 

[bborissov]

What type is CAVRESPOSTA field?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Albano]

Is smallint, is propuse is to recive only value of 0 and 1.

 

[bborissov]

When the field type is int (all kinds) the result of the aggregate functions also be int. AVG() will be int also. Try:

.....
SELECT
       AVG(CAST(CAVRESPOSTA as numeric(10,2))) MEDIA
FROM VISITASCRITERIOSAVALIACAO
WHERE
....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Albano]

thanks, it works.

Albano