July 21, 2017 at 1:45 pm
I have two columns which I need to divide in order to get a percentage. the tsql for getting these two numbers is complicated and I think it would be easier to perform this in SSIS inside a derived column. I thought I had the expression but I end up getting a 0 when it shouldn't be. what I have now is as follows:
(DT_STR,500,1252)(((DT_DECIMAL,0)([N&U with NPI] / [N&U] * 100.00))) + "%"
[N&U with NPI] and [N&U] are both datatypes DT_I4. Can someone explain what I'm doing to get a value of 0%?
July 21, 2017 at 2:24 pm
I could be wrong, but I think the problem is you are doing incorrect data type conversion.
You are dividing an int by an int which will produce an int. Since I expect that [N&U with NPI] is smaller than [N&U], you are getting 0 * 100.00 then casting that to decimal which would be 0.
10/100 = 0.1. Cast that to INT and you get 0.
So, what you will want to do is cast both of your DT_I4 values to DT_DECIMAL BEFORE you divide them.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply