Query result rounding down

Question

Query result rounding down

steve.chapman

Grasshopper

Points: 22
More actions
May 23, 2008 at 5:45 am

#190983

Hello,
I have a query that claculates the average number of days to ship an order.
SELECT CAST((b.[total shipping days]/c.[shipping days])AS NUMERIC(5,2)) AS [average working days]
FROM
(SELECT
SUM(DATEDIFF(d,[order date], [ship date])) AS [total shipping days]
FROM orders)b,
(SELECT
COUNT(*) AS [shipping days]
FROM orders
WHERE DATENAME(dw, [order date]) not in ('Saturday','Sunday'))c
When it runs, it rounds down the result to 3.00. In my testing, I know that the actual answer should be 3.96.
I would be very grateful if anyone could shed any light on what needs to be done to rectify this.
Thanks in advance.
Steve Chapman

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try this

SELECT CAST((CAST(b.[total shipping days] AS NUMERIC(5,2))/CAST(c.[shipping days] AS NUMERIC(5,2)))AS NUMERIC(5,2)) AS [average working days]

FROM

(SELECT

SUM(DATEDIFF(d,[order date], [ship date])) AS [total shipping days]

FROM orders)b,

(SELECT

COUNT(*) AS [shipping days]

FROM orders

WHERE DATENAME(dw, [order date]) not in ('Saturday','Sunday'))c

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Mark's right. Your syntax performs an INTEGER division, and once a result is derived (3), the result is cast to numeric. Without casting to a numeric first (even if just the numerator), the decimal portion will get truncated.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

steve.chapman Grasshopper Points: 22 More actions · Answer 3

Thanks for your quick response,

I had already tried that and got an error. The error message is -:

Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting int to data type numeric.

Any help would be greatly appreciated.

Steve Chapman

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

that would mean that you have some numbers that are larger that 999. You'd want to make the numeric "larger", so that it can accomodate the number of digits involved.

so - try something like numeric(18,2).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

steve.chapman Grasshopper Points: 22 More actions · Answer 5

Thank you very much for your help. This has solved the problem.

Best Regards,

Steve Chapman

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

1.0 * b.[total shipping days] / c.[shipping days] AS ...

N 56°04'39.16"
E 12°55'05.25"

steve.chapman Grasshopper Points: 22 More actions · Answer 7

Thank you,

1.0 * b.[total shipping days] / c.[shipping days] works as well if you want to display more than 1 decimal place.

Best Regards,

Steve Chapman

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

steve.chapman (5/23/2008)
Thank you,
1.0 * b.[total shipping days] / c.[shipping days] works as well if you want to display more than 1 decimal place.
Best Regards,
Steve Chapman

It does - just keep in mind with Peso's result, you'd be dealing with a FLOAT instead of a NUMERIC (one's "precise" and one's not). It could make for some differences in your results, so choose carefully.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?