Link to home
Start Free TrialLog in
Avatar of jammy-d0dger
jammy-d0dger

asked on

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

Avatar of jammy-d0dger
jammy-d0dger

ASKER

sorry. I should add that I have left some of the where clause out of the stored proc as it's not relevant.
ASKER CERTIFIED SOLUTION
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys, both solutions/approaches work perfectly.  I've awarded slightly more to the quickest answer.  Many thanks to you both.