February 5, 2015 at 5:38 am
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)
);
February 5, 2015 at 6:20 am
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
February 5, 2015 at 11:22 am
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
February 5, 2015 at 11:25 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2015 at 11:52 am
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
February 5, 2015 at 11:59 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2015 at 12:27 pm
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.
February 5, 2015 at 12:37 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2015 at 1:03 pm
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
February 5, 2015 at 1:07 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply