May 15, 2013 at 8:31 pm
Dear friends,
I need to do a urgent requirement-
I'm facing issues with rounding the duration to a whole number. The new requirement is: Calculate as (Job Posting End Date - Job Posting Start Date)/30. Round to the nearest two decimals. OR Calculate as (Work Order End Date - Work Order Start Date)/30. Round to the nearest two decimals. So per this updated requirement:
-if the calculation came to 20.42, it should display as 20.42
-if the calculation came to 20.4789, it should display as 20.48
-if the calculation came to 20.4739, is should display as 20.47
-if the calculation came to 20.1234, it should display as 20.12
BUT my code is-
CAST(floor(datediff(d, [WorkForce_JobPosting].[Job Posting Start Date_JP], [WorkForce_JobPosting].[Job Posting End Date_JP]) / 30) AS INT)
and is not working it's giving me the above results.
Need help please.
Thanks
Dhananjay
May 15, 2013 at 8:57 pm
dhananjay.nagarkar (5/15/2013)
Dear friends,I need to do a urgent requirement-
I'm facing issues with rounding the duration to a whole number. The new requirement is: Calculate as (Job Posting End Date - Job Posting Start Date)/30. Round to the nearest two decimals. OR Calculate as (Work Order End Date - Work Order Start Date)/30. Round to the nearest two decimals. So per this updated requirement:
-if the calculation came to 20.42, it should display as 20.42
-if the calculation came to 20.4789, it should display as 20.48
-if the calculation came to 20.4739, is should display as 20.47
-if the calculation came to 20.1234, it should display as 20.12
BUT my code is-
CAST(floor(datediff(d, [WorkForce_JobPosting].[Job Posting Start Date_JP], [WorkForce_JobPosting].[Job Posting End Date_JP]) / 30) AS INT)
and is not working it's giving me the above results.
Need help please.
Thanks
Dhananjay
The calculation should look like this:
round(datediff(d, [WorkForce_JobPosting].[Job Posting Start Date_JP], [WorkForce_JobPosting].[Job Posting End Date_JP]) / 30.0, 2)
Based on the numbers shown above, if you run this code you will get what you expect:
--if the calculation came to 20.42, it should display as 20.42
--if the calculation came to 20.4789, it should display as 20.48
--if the calculation came to 20.4739, is should display as 20.47
--if the calculation came to 20.1234, it should display as 20.12
with BaseData as ( -- This is a CTE to provide base data
select MyValue
from (values (20.42),(20.4789),(20.4739),(20.1234))dt(MyValue))
select
round(MyValue, 2)
from
BaseData;
May 16, 2013 at 4:47 am
Thanks Lynn it worked 🙂 you are awesome.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply