Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • Last Modified:

basic maths division in sql stored procedure

Hi experts,

See in the code that I have a stored proc that successfully sets @totalOrderCount to the correct value in the first block but when used in the second select statement as part of a math column, it fails.  I've proven that both the elements in this maths column are working by putting them in as sepratae columns in their own right, but the actual maths column of: "(COUNT(o.orders_id) / @totalOrderCount) AS orderPC" just returns zeroes.

132      0      0      957
189      1      0      957
54      2      0      957
131      3      0      957
120      4      0      957
84      5      0      957
87      6      0      957
92      7      0      957
22      8      0      957
26      9      0      957
18      10      0      957
2      11      0      957

Confused... ?
DECLARE @totalOrderCount INT

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	
	--SET NOCOUNT ON;
	
	
	SET @totalOrderCount = (SELECT COUNT(o.orders_id)
	FROM orders O 
	WHERE o.orders_status = 4 
	AND DATEDIFF(DAY, o.date_purchased, GETDATE()) < 31)

SELECT COUNT(o.orders_id) AS ordercount, (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp)) AS timetaken,
(COUNT(o.orders_id) / @totalOrderCount) AS orderPC, @totalOrderCount AS orderTotal
FROM orders O, dbo.tblPS_orders_status_history OSH WHERE
o.orders_status = 4

Open in new window

0
jammy-d0dger
Asked:
jammy-d0dger
  • 2
2 Solutions
 
jammy-d0dgerAuthor Commented:
sorry. I should add that I have left some of the where clause out of the stored proc as it's not relevant.
0
 
deightonCommented:
cast as float prior to division, and multiply by 100 perhaps to get percentage

SELECT COUNT(o.orders_id) AS ordercount, (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp)) AS timetaken,
(cast(COUNT(o.orders_id) as float) / cast(@totalOrderCount as float)) AS orderPC, @totalOrderCount AS orderTotal
FROM orders O, dbo.tblPS_orders_status_history OSH WHERE
o.orders_status = 4

Open in new window

0
 
LowfatspreadCommented:
i guess you are doing intefer arithmetic and losing precision..

either convert the operand to a more  precise data type or start off by multiplying the top
by 1.00

(1.00*count(o.orders_id))/@totalordercount
0
 
jammy-d0dgerAuthor Commented:
Thanks guys, both solutions/approaches work perfectly.  I've awarded slightly more to the quickest answer.  Many thanks to you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now