Add IF condition to WHERE clause

Question

Post reply

Add IF condition to WHERE clause

JJR333

Default port

Points: 1469
More actions
February 5, 2015 at 5:38 am

#297463

Hi,
I want to add an exception to the WHERE clause below.
Something like:
IF @IDHuis = 'WH' THEN
add the extra condtion:
AND D.AfdelingZPT <> 'A01'
Any suggestions?
Cheers,
Julian
-- HRS VAST
INSERT INTO @T (JAAR, WEEK, HrsVast)
(
SELECTDBO.ISOyear(Begindatum) Jaar, datepart(ISO_WEEK,Begindatum) [Week], sum(DUUR/60.0) HrsVast
FROM DRPDATA D
JOIN HuisAfdeling H
OND.LocatieCode = H.IDHuis
AND D.AfdelingZPT = H.IDAfd
WHERE LocatieCode = @IDHuis
and H.MeeTellen <> 'NEE'
AND DBO.ISOyear(Begindatum) = @Jaar
and OEGroep = 'zorg'
AND NiveauZPT IN ('1','2','2+','3','4','5')
and FlexVast = 'vast'
and DienstGroep = 'Dag/Avond'
GROUP BY DBO.ISOyear(Begindatum), datepart(ISO_WEEK,Begindatum)
);

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

Try the below solution

-- HRS VAST

INSERT INTO @T (JAAR, WEEK, HrsVast)

(

SELECTDBO.ISOyear(Begindatum) Jaar, datepart(ISO_WEEK,Begindatum) [Week], sum(DUUR/60.0) HrsVast

FROM DRPDATA D

JOIN HuisAfdeling H

OND.LocatieCode = H.IDHuis

AND D.AfdelingZPT = H.IDAfd

WHERE LocatieCode = @IDHuis

and H.MeeTellen <> 'NEE'

AND DBO.ISOyear(Begindatum) = @Jaar

and OEGroep = 'zorg'

AND NiveauZPT IN ('1','2','2+','3','4','5')

and FlexVast = 'vast'

and DienstGroep = 'Dag/Avond'

and D.AfdelingZPT <> (case when @IDHuis='WH' then 'A01' end)

GROUP BY DBO.ISOyear(Begindatum), datepart(ISO_WEEK,Begindatum)

);

please note , I have not taken the else part

JJR333 Default port Points: 1469 More actions · Answer 2

Hello pmadhavapeddi22,

Thank you for pointing me in the right direction.

If the condition is not met (@IDHuis='WH') then I need to sum all hours from all D.AfdelingZPT.

So I changed the code as below. I seem to be getting the correct results.

and D.AfdelingZPT <> (case when @IDHuis='WH' then 'A01' else '' end)

Thanks for your help,

Julian

Netherlands

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

For efficiency, you might want to specify RECOMPILE on the proc and only test the D.AfdelingZPT column if you have to:

AND (@IDHuis, '' <> 'WH' OR D.AfdelingZPT <> 'A01')

If @IDHuis can be NULL, you may have to add a check for that too.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JJR333 Default port Points: 1469 More actions · Answer 4

Scott thank you.

I can't get my head around it though. 🙂

For one location (@IDHuis = 'WH')

I need to exclude the Hrs from the sum of one department (D.AfdelingZPT = 'A01') within that location.

All locations have the same department codes (eg. 'A01')

Cheers,

Julian

ScottPletcher SSC Guru Points: 101126 More actions · Answer 5

JJR333 (2/5/2015)
Scott thank you.
I can't get my head around it though. 🙂
For one location (@IDHuis = 'WH')
I need to exclude the Hrs from the sum of one department (D.AfdelingZPT = 'A01') within that location.
All locations have the same department codes (eg. 'A01')
Cheers,
Julian

I was basing my code on your initial requirement:

IF @IDHuis = 'WH' THEN
add the extra condtion:
AND D.AfdelingZPT <> 'A01'

To me, that means only if @IDHuis = 'WH' do you need to check D.AfdelingZPT.

So my code says:

if @IDHuis is not = 'WH' then select the row, and ignore the check on D.AfdelingZPT; but if = 'WH', then also check to see if D.AfdelingZPT <> 'A01' and, only if so, select the row.

Not logic is always difficult, but SQL can pre-resolve a variable in a query if the query is recompiled, whereas the CASE statement against a column must always be evaluated, and can cause unforeseen additional work in the query plan SQL generates.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JJR333 Default port Points: 1469 More actions · Answer 6

ScottPletcher (2/5/2015)
if @IDHuis is not = 'WH' then select the row, and ignore the check on D.AfdelingZPT; but if = 'WH', then also check to see if D.AfdelingZPT <> 'A01' and, only if so, select the row.

I get the NOT, but not the OR, which kicks in when the first condition (<>'WH') is not being met.... :ermm:

AND (@IDHuis <> 'WH' OR D.AfdelingZPT <> 'A01')

does give the desired results.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 7

JJR333 (2/5/2015)
ScottPletcher (2/5/2015)
if @IDHuis is not = 'WH' then select the row, and ignore the check on D.AfdelingZPT; but if = 'WH', then also check to see if D.AfdelingZPT <> 'A01' and, only if so, select the row.
I get the NOT, but not the OR, which kicks in when the first condition (<>'WH') is not being met.... :ermm:
AND (@IDHuis <> 'WH' OR D.AfdelingZPT <> 'A01')
does give the desired results.

Yes. The key is that SQL can "short-circuit" (bypass) an OR condition if the first condition is true. Say I write:

WHERE a = 1 OR b = 2

once "a = 1" is known to be true, SQL can, and almost always does, skip evaluating "b = 2", since T OR <any result> will still be true.

But, say "a = 1" is F (or NULL). Then SQL must evaluate "b = 2" and that condition will control whether the row is selected or not: if it's true, the row is in, else not.

We want SQL to short-circuit in our case especially, since checking a never-changing variable will be less overhead than checking a column in every row. Thus, we write the conditions such that they are connected by OR.

But, when @IDHuis <> 'WH', then SQL will have to check the D.AfdelingZPT column, which will give us the final result we want.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JJR333 Default port Points: 1469 More actions · Answer 8

Thank you Scott. It is slowly dawning.

The first NOT clause makes all the difference.

Is this T-SQL logic different from say Excel VBA logic?

SUM the turnover of all stores NOT in NY, OR NOT the Vegetable department.

Cheers,

Julian

ScottPletcher SSC Guru Points: 101126 More actions · Answer 9

JJR333 (2/5/2015)
Thank you Scott. It is slowly dawning.
The first NOT clause makes all the difference.
Is this T-SQL logic different from say Excel VBA logic?
SUM the turnover of all stores NOT in NY, OR NOT the Vegetable department.
Cheers,
Julian

I couldn't say for sure, I haven't studied Excel VBA's like I have SQL's.

But, in general most modern languages short-circuit whenever possible, since it speeds up processing. The computer just has to be sure that the results will be still be accurate.

As an aside, IBM's DB2 optimizer can do some incredible truly full rewrites of logic to gain efficiency. IBM's in the lead in this area.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".