News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... Using INT Value in Mat...

Finance | Project Operations, Human Resources, ...

Answered

Using INT Value in Math - SQL

(0) Share

Report

Report

Posted on by Bill Campbell

22,647

Ok, someone out there knows how to do this - I just can not find the right combination of SQL to make it work.

Field : PUnits - int

Values: 6000, 5000, 900, 50

What I know is to do the math to get from PUnits * PRate = PLineTotal

need to divide Punits by 100 to get to the right decimal value

SO

6000 = 60.00
5000 = 50.00
900 = 9.00
50 = 0.00

(PRate *(PUnits/100)) -- all work when the value in PUnits has 3 or more characters -

but when the PUnits value = 50 and I apply the same rule then the value ( PUnits/100 ) 50 / 100 = 0000

I am sure that it is a matter of figuring out the right way to 'format' PUnits first.

Thanks.

Categories:

Microsoft Dynamics GP

All responses Img

All responses (4)

Answers Img

Answers (1)

James McCullough 294 on at

Like (0)

Report

RE: Using INT Value in Math - SQL

And HEY! You're a fellow Albertan! I'm in Calgary! :D
James McCullough 294 on at

Like (0)

Report

RE: Using INT Value in Math - SQL

Hey Bill:

Could you hit it as a "verified answer" then please? I appreciate it greatly, as I'm just effectively getting started here. :)
Bill Campbell 22,647 on at

Like (0)

Report

RE: Using INT Value in Math - SQL

Thanks that got me the desired result.
Verified answer

James McCullough 294 on at

Like (0)

Report
RE: Using INT Value in Math - SQL

This is the way I just did it:

SELECT CONVERT(DECIMAL, 50) / CONVERT(DECIMAL, 100.00)

I mean, it's heavy handed, but it will do it. The problem is that SQL thinks that 50 and 100 are integers, and so is enforcing integer math on it.

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,214 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans