Sue Taylor
asked on
SQL Calculated Field CEILING or ROUNDUP
SSRS 2012 - Visual Studio 2012 - SQL 2012
I have a field called "Trend" and a field called "Multiplier" that I was to multiple together. Then I need to take the sum of those two fields and roundup to a multiple of a field called "Inv_Max"
For example: Trend is 1572 and multiplier is 3. (1572 * 3 = 4716) The Inv_Max field is 2400 so I want to roundup the 4716 to 4800, which is a multiple of Inv_max.
I want to be able to add a calculated field and can't figure out the formula.
I have a field called "Trend" and a field called "Multiplier" that I was to multiple together. Then I need to take the sum of those two fields and roundup to a multiple of a field called "Inv_Max"
For example: Trend is 1572 and multiplier is 3. (1572 * 3 = 4716) The Inv_Max field is 2400 so I want to roundup the 4716 to 4800, which is a multiple of Inv_max.
I want to be able to add a calculated field and can't figure out the formula.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Logic is: multiply them then divide. Round up the remainder and multiple that by the max again:
select ceiling(cast(trend as float)*cast(multiplier as float)/inv_max)*inv_max from your_table;