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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 12, 2014 at 7:05 am

  #1751860

  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

  October 12, 2014 at 7:26 am

  #1751862

  Great, thank you Lutz.

  Cheers,

  Julian