July 13, 2010 at 3:49 am
Ok, I'm new to SQL and trying to fix a database call in a script our team is using. The one who created the script is only 2 weeks in his 3 month holiday :w00t: so waiting for him to return is, unfortunately, not really an option.
I already found out that the issue is in this piece of code:
/ CONVERT(FLOAT, EXPECTEDDURATION)
We now hit the odd situation in which EXPECTEDDURATION is zero. I already tried to use ISNULL( EXPECTEDDURATION, 1) but that doesn't work... I reckon the 2nd parameter is being returned when ISNULL = TRUE.
The value in the EXPECTEDDURATION column is a straight forward 0 (not a null) so that is most likely the cause that the ISNULL isn't working properly.
Is there any way I can check for the EXPECTEDDURATION to be zero and then have it turned into a 1 for example?
All suggestions are welcome. Thanks.
July 13, 2010 at 3:56 am
You can use CASE but be VERY VERY careful.
You should check with the business what they want to happen when EXPECTEDDURATION is zero. It could be that there has been a mistake in the calculation of EXPECTEDDURATION which needs to be examined.
July 13, 2010 at 3:58 am
DECLARE @EXPECTEDDURATION INT
SET @EXPECTEDDURATION = 0
IF @EXPECTEDDURATION = 0
BEGIN
SELECT CONVERT(FLOAT, 1) / CONVERT(FLOAT, 1)
END
ELSE
SELECT CONVERT(FLOAT, 1) / CONVERT(FLOAT, @EXPECTEDDURATION)
July 13, 2010 at 4:01 am
Ok, I will dive into the CASE thingy to see if that will help me.
The 'business' is our team. We are now using a couple of our own web interfaces to monitor a bunch of queues as the original interface is terribly slow and will kick you out every 15 minutes and since we hate to type our credentials about a zillion times a day we came up with this.
July 13, 2010 at 4:04 am
Welcome to SSC!
You can use a CASE statement to convert the 0 to a 1:
DECLARE @EXPECTEDDURATION int
SET @EXPECTEDDURATION = 20
SELECT 1000.00 / CONVERT(FLOAT, CASE WHEN @EXPECTEDDURATION = 0 THEN 1 ELSE @EXPECTEDDURATION END)
Note that your code uses a column named EXPECTEDDURATION instead of the variable I've used (@EXPECTEDDURATION)
... And after hitting preview, I see more replies already. I agree completely with Ken - it is possible that 0 is a valid rule and should be handled differently.
skcadavre, you've done almost exactly what mine did, but if the OP's code is part of a query, then a set-based solution (CASE) is necessary.
July 13, 2010 at 4:23 am
Jim McLeod (7/13/2010)
skcadavre, you've done almost exactly what mine did, but if the OP's code is part of a query, then a set-based solution (CASE) is necessary.
I'll be honest, I didn't think of it. But, I'm here to learn so always nice to be corrected.
July 13, 2010 at 5:34 am
Thanks all for your quick assistance!!
I ended up with:
CONVERT(INT, 100 * (CASE EXPECTEDDURATION WHEN 0 THEN 0 ELSE 1.0 END - CONVERT(FLOAT,
(dbo.fn_GetWorkingDays(TASKSTARTDATE, GetDate())* 8)) / CONVERT(FLOAT, CASE
EXPECTEDDURATION WHEN 0 THEN 8 ELSE EXPECTEDDURATION END))) AS PERCENTAGELEFT
to reflect everything correctly - at least it will do the trick for now :satisfied:
July 13, 2010 at 10:51 am
How about the following solution?
SELECT 5/(ISNULL(NULLIF(EXPECTEDDURATION,0),1))
The NULLIF() function will return NULL if EXPECTEDDURATION is Zero. Fortunately, a devision by Zero is allowed and will return NULL. But since you don't want to get NULL as the result, I wrapped the ISNULL() function around the NULLIF() function to return 1 in case EXPECTEDDURATION is Zero.
Someone might find it easier to read than the CASE statement. Others won't...
A few more issues regarding your final query:
1) What is the original data type of EXPECTEDDURATION and why do you need to convert it to FLOAT? If EXPECTEDDURATION is an INT value and you just want to get an intermediate return value in decimal format, you could use:
SELECT CONVERT(INT, 100 *
(CASE EXPECTEDDURATION WHEN 0 THEN 0 ELSE 1.0 END
- (dbo.fn_GetWorkingDays(TASKSTARTDATE, GETDATE())* 8.0)
/(ISNULL(NULLIF(EXPECTEDDURATION,0),8) + 0.0)
)
) AS PERCENTAGELEFT
2) Would you mind posting the code of dbo.fn_GetWorkingDays? There might be a chance of changing it into an inline Table Valued function (AKA iTVF) to improve performance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply