Conditions in SELECT
-
I have a fun T-SQL question for anyone that needs a challenge. This below works fine for most cases, but there are two potentials for a divide by zero error. I would like to have a different result based on the condition.
If GRDTOT, GENBUD, APPSPC, DHMBB, NBCCOL, and NTSCOL are all 0, then I want statPercent to be 0.
If GRDTOT = (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) then I want statPercent to be 100.
Any ideas?
SELECT
wkey,
STATYR,
'N' AS overrideFlag,
ROUND (100 * ((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / (GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL))), 2 ) AS statPercent,
0 AS overridePercent
FROM WKSTAT
WHERE
STATYR = @statyr AND
(100 * ((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / (GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL))) > 10) AND
WKDST = @district
The Redneck DBA
-
Use a case statement (or several).
EG
(ps, formatting your code as "Formatted" makes it a LOT easier for us to read & understand)
SELECT wkey, STATYR, 'N' AS overrideFlag, Case When GRDTOT = (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) Then Case When GRDTOT=0 Then 0 Else 100 End Else ROUND (100 * ((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / (GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL))), 2 ) End AS statPercent, 0 AS overridePercent FROM WKSTAT WHERE STATYR = @statyr AND ( (GRDTOT = (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL)) OR (100 * ((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / (GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL))) > 10) ) AND WKDST = @district
I've accounted for your case in both the select statement with two nested cases and in your where clause. I haven't tested it so you'll want to put some data in that could cause divide by zero errors. If you do get errors it is probably because of the division still occurring in the where clause but I am hoping the OR will take care of that. If it doesn't, you'll need to put a case statement in the where clause too!
Cheers!
-
You can't rely on SQL using a lazy OR, i.e. ignoring the second disjunct if the first is true. Even if it did sometimes do that, which I don't think it does, and even if your code prevented the div/0 from being evaluated, which it doesn't, you can't assume it will always work. CASE is the correct way to avoid evaluating certain expressions, so you probably need to put it in the where clause.
If you have any performance issues there are some things you could experiment with, but I won't go into them unless you need to.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
SELECT
wkey,
STATYR,
'N' AS overrideFlag,
ROUND (100 * ISNULL(((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / NULLIF(GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL), 0)), 1), 2 ) AS statPercent,
0 AS overridePercent
FROM WKSTAT
WHERE
STATYR = @statyr AND
(100 * ISNULL(((GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL) / NULLIF(GRDTOT - (GENBUD + APPSPC + DHMBB + NBCCOL + NTSCOL), 0)), 1 > 10) AND
WKDST = @district
_____________
Code for TallyGenerator -
Very true Tim - you cannot rely on SQL doing that.. I have seen one case where someone proved that SQL does do a form of lazy boolean evaluation but it was under a strict condition, wasn't documented and was only proven in the case where SQL may not call a UDF if not required (the UDF was made to have a side-effect - cannot remember how that was done - so they could tell if it was called or not).
Thus you should also have a case in the where clause. I haven't properly looked at Sergiy's solution but it appears that it might work. It's a bit more elegant - depending on your pref that may or may not be better
The case is easier to understand as your different conditions are clearly labelled, however the NULLIF and ISNULL may perform better - you could only tell from looking at the execution plans.The other thing to point out is that is if you are planning to filter like this a lot, you could create an indexed view. Thus your eventual stat result is indexed saving your SQL Server many table scans and a lot of disk + CPU work if you have lots of rows in your table.
Cheers
-
Re indexing - I agree for the medium term, but on a longer view and if you have many such reports, you might want to look at a specialised reporting environment - which would not only help performance generally, but just as importantly, provide a way to define and maintain these kind of calculations.
Ian - I'd be interested to see that code if you ever track it down. BTW we are just toying with Sri Lanka, and intend to retain the urn this Aus-summer.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
hahaha - we'll see
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply