CASE
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.25 THEN (0.25+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.25 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.50 THEN (0.50+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.50 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.75 THEN (0.75+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.75 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<1 THEN (1+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))= 0 THEN (0+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
END
AS Estimated_Effort_Days,
The above code is currently rounding up a field called totaleffort to the nearest.25, for example if i have a value of 78.19 it will round up to 78.25.
I have a new requirement for the value of zero, when the value = 0 then i need to display the text 'unknown number' I have attempted to add an additional case statement however the query fails to run with an error :
Error converting data type varchar to float.
Does anyone have a reccomendation for me
First of all, your present code returns a number. And you are trying to add a condition when it should return a string. The problem is, numeric types take precedence over string types, and so, as a result, SQL Server will try to convert your string message to a number (and fail).
To avoid that, you should make sure that all numeric values you are returning are properly converted to strings, then you can easily add whatever message you want as a substitute for zeros.
Another thing is, your rounding technique seems to me overcomplicated. If you want to round up, just use CEILING(). If you want to round up to the nearest 0.25, you can multiply by 4, apply CEILING(), then divide by 4.
Here's my attempt at illustrating what I mean:
WITH data (totaleffort) AS (
SELECT CAST(123.5 AS float) UNION ALL
SELECT 88 UNION ALL
SELECT 0.067 UNION ALL
SELECT 0 UNION ALL
SELECT 9608.14
)
SELECT
ISNULL(
CAST(CAST(NULLIF(CEILING(totaleffort * 4 / 7.40) / 4, 0) AS decimal(10, 2)) AS nvarchar(30)),
'unknown number'
)
FROM data
Output:
------------------------------
16.75
12.00
0.25
unknown number
1298.50
You can also see that I'm using ISNULL() and NULLIF() here to replace 0 with a custom text. It works like this:
the calculation result is passed to NULLIF whose second argument is 0 – that means that if the result is 0, NULLIF will return NULL, otherwise it will return the result;
now ISNULL does the opposite: it returns the second argument if the first one is NULL, otherwise it returns the first argument.
So, with this chain of transformations a zero effectively becomes 'unknown number'.