Rounding to 2 Decimal places not working.

Question

Rounding to 2 Decimal places not working.

shezi

SSCommitted

Points: 1514
More actions
August 11, 2016 at 3:27 pm

#320601

The rounding works fine to two decimal places if I try to convert directly but when I put in a sub query then it doesn't do anything.
select CAST(391/60.00 as decimal(38, 2))
returns 6.52
but the query below gives me 6.51999998092651 and it is not rounding anything.
SELECT pp_id AS pay_period_id,
employee_no,
reg,
ovt
FROM (SELECT pp.id AS pp_id,
pr.employee_no,
CASE WHEN E.pay_type = 4 then pr.hours else CAST(391/60.00 as decimal(38,2)) END AS [hours],
rate_type
FROM pay_records AS pr
INNER JOIN pay_period pp ON pr.[date] BETWEEN pp.start_period AND pp.end_period AND valid_payroll = 0
INNER JOIN employee E ON E.Employee_no=pr.employee_no) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([REG],
[OVT])) AS pv

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

shezi (8/11/2016)
The rounding works fine to two decimal places if I try to convert directly but when I put in a sub query then it doesn't do anything.
select CAST(391/60.00 as decimal(38, 2))
returns 6.52
but the query below gives me 6.51999998092651 and it is not rounding anything.
SELECT pp_id AS pay_period_id,
employee_no,
reg,
ovt
FROM (SELECT pp.id AS pp_id,
pr.employee_no,
CASE WHEN E.pay_type = 4 then pr.hours else CAST(391/60.00 as decimal(38,2)) END AS [hours],
rate_type
FROM pay_records AS pr
INNER JOIN pay_period pp ON pr.[date] BETWEEN pp.start_period AND pp.end_period AND valid_payroll = 0
INNER JOIN employee E ON E.Employee_no=pr.employee_no) AS p
PIVOT (Sum([hours])
FOR rate_type IN ([REG],
[OVT])) AS pv

Before I pursue an answer I have a question. What is the data type for this column, pr.hours, in the above query?

shezi SSCommitted Points: 1514 More actions · Answer 2

pr.Hours is real and where you see the hardcoded value that is also column(ActualHours) which is integer but I hardcoded the value though.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shezi (8/11/2016)
pr.Hours is real and where you see the hardcoded value that is also column(ActualHours) which is integer but I hardcoded the value though.

With a simple setup I can't get what you do. What I would suggest is to use the round function instead of casting as decimal(38,2) value.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

In your CASE statement, try changing "pr.hours" to "CAST(pr.hours as decimal(38,2))".

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Alvin Ramard (8/12/2016)
In your CASE statement, try changing "pr.hours" to "CAST(pr.hours as decimal(38,2))".

You are going down the path I was, just couldn't recreate the problem with a simple setup.

shezi SSCommitted Points: 1514 More actions · Answer 6

that was the issue. I also had to cast or round the "pr.hours" field as well otherwise it wouldn't round the hard coded value i had in there. Thanks Guys!