Not inserting as decimal - 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

General SQL Server Forums

New to SQL Server Programming

Not inserting as decimal

Author

Topic

will0122
Starting Member

11 Posts

Posted - 2009-07-07 : 16:08:45

Hi,
I am tring to insert the results of a query into a table but the Score (Numeric(4,0)) is not inserting into “Score (numeric(4,0))” as decimal it rounds up the number. Any idea?

insert into calificaciones (RCAF, Score, Date)
SELECT rtrim(a.rcaf) as [RCAF],
convert (decimal(4,2),avg(a.score * 1.0)) AS [Average],
CONVERT(varchar(10), GETDATE(), 1) AS [Date]

FROM (SELECT rcaf,
score,
[ReviewdON],
Row_number()
OVER(PARTITION BY rcaf ORDER BY [ReviewdON] DESC) AS seq
FROM VerintInfo) a
WHERE a.seq <= 5
GROUP BY a.rcaf

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 16:13:55

What NUMERIC(4, 0)?
I see NUMERIC(4, 2).

N 56°04'39.26"
E 12°55'05.63"

will0122
Starting Member

11 Posts

Posted - 2009-07-07 : 16:18:52

Well on the table is the type is numberic(4,0), I just tried with the convert just to see what happened.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-07 : 16:26:37

Well you are using a DECIMAL(4,0) in order to do what I think you want, you'll need to change the data type to allow decimal points.

According to BOL:
decimal[ (p[ , s] )]

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

will0122
Starting Member

11 Posts

Posted - 2009-07-07 : 16:32:34

when i remove the insert part of the code it does give me the results in decimals but when i try to insert it into a table they get stored as number without decimal. they Data type on the new table is numeric(4, 0).

--insert into calificaciones (RCAF, Score, Date)
SELECT rtrim(a.rcaf) as [RCAF],
avg(a.score * 1.0) AS [Average],
CONVERT(varchar(10), GETDATE(), 1) AS [Date]

FROM (SELECT rcaf,
score,
[ReviewdON],
Row_number()
OVER(PARTITION BY rcaf ORDER BY [ReviewdON] DESC) AS seq
FROM VerintInfo) a
WHERE a.seq <= 5
GROUP BY a.rcaf

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 16:37:54

What about this?

insert into calificaciones (RCAF, Score, Date)
SELECT rtrim(a.rcaf) as [RCAF], 
convert (decimal(4, 0),avg(a.score * 1.0)) AS [Average],
CONVERT(varchar(10), GETDATE(), 1) AS [Date]

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-07 : 16:47:47

Again, when you specify a Scale of 0 (Zero) you do not get any digits after the decimal point:

DECLARE @Foo TABLE (Val DECIMAL(4,2))

INSERT @Foo
SELECT 1.4
UNION ALL SELECT 12.23
UNION ALL SELECT 43.89
UNION ALL SELECT 99.99

SELECT *
FROM @Foo

Val
------
1.40
12.23
43.89
99.99

SELECT CAST(Val AS DECIMAL(4,0)) AS Val
FROM @Foo

Val
----
1
12
44
100

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 16:52:29

Also, why are you using GETDATE() instead of reviewedON?

INSERT		Calificaciones
		(
			RCAF,
			Score,
			Date
		)
SELECT		rcaf, 
		AVG(score * 1.0E) AS [Average],
		CONVERT(VARCHAR(10), GETDATE(), 1)
FROM		(
			SELECT	rcaf, 
				score, 
				Row_number() OVER (PARTITION BY rcaf ORDER BY [ReviewdON] DESC) AS seq 
			FROM	VerintInfo
		) AS a 
WHERE		seq <= 5 
GROUP BY	rcaf

N 56°04'39.26"
E 12°55'05.63"

will0122
Starting Member

11 Posts

Posted - 2009-07-07 : 17:02:22

I use get date because i need the date when the average was calculated and the ReviewdOn has the date and time of the evaluation.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 17:09:06

If you want the average per day?

INSERT		Calificaciones
		(
			RCAF,
			Score,
			Date
		)
SELECT		rcaf, 
		AVG(score * 1.0E) AS [Average],
		[ReviewdON]
FROM		(
			SELECT	rcaf, 
				score, 
				DATEADD(DAY, DATEDIFF(DAY, 0, [ReviewdON]), 0) AS [ReviewdON], 
				ROW_NUMBER() OVER (PARTITION BY rcaf, DATEADD(DAY, DATEDIFF(DAY, 0, [ReviewdON]), 0) ORDER BY [ReviewdON] DESC) AS seq 
			FROM	VerintInfo
		) AS a 
WHERE		seq <= 5 
GROUP BY	rcaf,
		[ReviewdON]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 17:09:57

Until you read this blog post, all we can do to assist you is educated guesses.
See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

N 56°04'39.26"
E 12°55'05.63"

will0122
Starting Member

11 Posts

Posted - 2009-07-07 : 17:36:08

Thank you very much guys the problem was that on the calificaciones table the score data type was numeric(4,0) and i changed it like you said nmeric(4,2) and now it worked. thanks a lot guys, i will read the article anyway. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources