Number format issue in data designer

[toon10]

I have the following SQL command which I’ve written in the Visual Basic 6 data designer. It’s looking at a SQL 2005 database.

SELECT viewInjectorPipe1.S1TestDate,
SUM(viewInjectorPipe1.[CountOfInjector Pipe Moved] / viewInjectorPipe2.CountOfSerNo)
AS InjPipe
FROM viewInjectorPipe1 INNER JOIN
viewInjectorPipe2 ON
viewInjectorPipe1.S1TestDate = viewInjectorPipe2.S1TestDate
GROUP BY viewInjectorPipe1.S1TestDate
HAVING (viewInjectorPipe1.S1TestDate BETWEEN GETDATE()
- 365 AND GETDATE())
ORDER BY viewInjectorPipe1.S1TestDate

The InjPipe column returns 0 every time. (I’m expecting results like 0.091234, etc) If I replace the “/” symbol with “+”, “-“ or “*”, the statement works fine.

I’m presuming that because the division will cause the figure to be less than 1, the problem has something to do with the number format of InjPipe. Maybe it’s rounding down?

Does anybody know how I can convert what I have so that the InjPipe column will return numbers like 0.09342?

Thanks

 

[AlexCuse]

Sounds like SQL server is doing integer math. Try doing something like this:

SUM(convert(decimal (10,2),viewInjectorPipe1.[CountOfInjector Pipe Moved]) / convert(decimal (10,2), viewInjectorPipe2.CountOfSerNo))
AS InjPipe

You may need to fool with the scale and precision a bit.

hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[toon10]

That seems to work a treat. Thanks for the help, it's much appreciated.

Andrew

 

[AlexCuse]

Glad it worked! If you need more decimal places, have al ook in SQL Server books online about the decimal data type.

Alex

Ignorance of certain subjects is a great part of wisdom