UNION followed by IF ELSE

Question

UNION followed by IF ELSE

JJR333

Default port

Points: 1469
More actions
October 12, 2014 at 6:56 am

#311803

I get a red squiggle below the first IF statement.
Can't figure out the correct syntax.
Any suggestions?
TIA,
Julian
Netherlands
UNION ALL
IF ((SELECT Flexpool FROM Huizen WHERE IDHuis = @LOCATIECODE ) = 'WAAR')
SELECTM.LOCATIECODE, M.AfdelingZPT, DATEPART(ISO_WEEK, M.Begindatum) wk, M.DienstGroep, NULL Leeg, NULL Leeg, SUM(M.Hrs) AS Uren
FROMDRPHrsManual M
WHEREM.LocatieCode = @LocatieCode
AND M.AfdelingZPT = @Afdeling
AND M.Begindatum between @BeginDatum and @BeginDatum+6
AND M.DienstGroep = 'FLEXPOOL'
AND M.NiveauZPT <> 'STA'
AND M.NiveauZPT IN ('1','2','2+','3','4','5')
GROUP BY M.LocatieCode, DATEPART(ISO_WEEK, M.Begindatum), M.AfdelingZPT, M.DienstGroep
ELSE
-- FINISH THE CODE BELOW....
SELECT D.LOCATIECODE, D.AfdelingZPT, DATEPART(ISO_WEEK, D.Begindatum) WK, D.DienstGroep, NULL Leeg, NULL Leeg, SUM(D.Duur)/60.0 AS UREN
FROMDRPDATA D
WHERED.LocatieCode = @LocatieCode
--AND D.AfdelingZPT = @Afdeling
AND D.Begindatum between @BeginDatum and @BeginDatum+6
AND D.DienstGroep = 'DAG/AVOND'
AND D.FlexVast = 'FLEX'
AND D.NiveauZPT <> 'STA'
AND D.NiveauZPT IN ('1','2','2+','3','4','5')
GROUP BY D.LocatieCode, DATEPART(ISO_WEEK, D.Begindatum), D.AfdelingZPT, D.DienstGroep
-- GET PNIL FLEX
UNION ALL

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I recommend to assign the result of the query against Huizen in a separate variable and use that result in the query to eliminate the IF .. ELSE control flow.

SELECT @Flexpool = Flexpool FROM Huizen WHERE IDHuis = @LOCATIECODE

...

UNION ALL

SELECTM.LOCATIECODE, M.AfdelingZPT, DATEPART(ISO_WEEK, M.Begindatum) wk, M.DienstGroep, NULL Leeg, NULL Leeg, SUM(M.Hrs) AS Uren

FROMDRPHrsManual M

WHEREM.LocatieCode = @LocatieCode

AND M.AfdelingZPT = @Afdeling

AND M.Begindatum between @BeginDatum and @BeginDatum+6

AND M.DienstGroep = 'FLEXPOOL'

AND M.NiveauZPT <> 'STA'

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

AND @Flexpool = 'WAAR'

GROUP BY M.LocatieCode, DATEPART(ISO_WEEK, M.Begindatum), M.AfdelingZPT, M.DienstGroep

UNION ALL

-- FINISH THE CODE BELOW....

SELECT D.LOCATIECODE, D.AfdelingZPT, DATEPART(ISO_WEEK, D.Begindatum) WK, D.DienstGroep, NULL Leeg, NULL Leeg, SUM(D.Duur)/60.0 AS UREN

FROMDRPDATA D

WHERED.LocatieCode = @LocatieCode

--AND D.AfdelingZPT = @Afdeling

AND D.Begindatum between @BeginDatum and @BeginDatum+6

AND D.DienstGroep = 'DAG/AVOND'

AND D.FlexVast = 'FLEX'

AND D.NiveauZPT <> 'STA'

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

AND @Flexpool <> 'WAAR'

GROUP BY D.LocatieCode, DATEPART(ISO_WEEK, D.Begindatum), D.AfdelingZPT, D.DienstGroep

-- GET PNIL FLEX

UNION ALL

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

JJR333 Default port Points: 1469 More actions · Answer 2

JJR333

Default port

Points: 1469

October 12, 2014 at 7:26 am

#1751862

Great, thank you Lutz.

Cheers,

Julian