SELECT FROM SUBQUERYS

• JJR333

  Default port

  Points: 1469

  More actions

  October 31, 2014 at 9:27 am

  #291628

  The first Select From works ok on its own.

  For some reason I cannot get the last (3th) column (see bottom of the code) to work.

  Any suggestions?

  TIA,

  Julian / Netherlands

  SELECTAfdelingZPT

  ,SUM(Uren) INZET

  FROM(

  --GET INZET FROM DRP, DAG/AVOND & VAST

  SELECTLOCATIECODE, AfdelingZPT, DATEPART(ISO_WEEK, Begindatum) wk, DienstGroep, FlexVast, OEGroep, sum(cast(DUUR as float))/60 as Uren

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='DAG/AVOND'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  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 dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  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

  -- GET PNIL FLEX

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'PNILFLEX'

  AND M.NiveauZPT = 'PNIL'

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

  -- GET HRS NIET IN DRP

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'NOTINDRP'

  AND M.NiveauZPT = 'NID'

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

  -- GET INLEEN HRS

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'INLEEN'

  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

  -- GET UITLEEN HRS

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'UITLEEN'

  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

  ) foo

  ,SUM(Overig)

  FROM

  (

  SELECTLOCATIECODE, AfdelingZPT, DATEPART(ISO_WEEK, Begindatum) wk, DienstGroep, FlexVast, OEGroep, sum(cast(DUUR as float))/60 as Overig

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='Overig'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  ) fob

  GROUP BYLocatieCode, AfdelingZPT

• sqlslacker

  SSChasing Mays

  Points: 657

  More actions

  October 31, 2014 at 9:52 am

  #1756071

  You can only have one FROM in your query. I believe you'll want your SUM(Overig) up above the first FROM. You may have to do SUM(fob.Overig) as well.

• JJR333

  Default port

  Points: 1469

  More actions

  October 31, 2014 at 10:08 am

  #1756080

  Thanks for your reply.

  Can't get it to work.

  I think a MERGE will to the trick.

  Cheers,

  Julian

• sqlslacker

  SSChasing Mays

  Points: 657

  More actions

  October 31, 2014 at 10:40 am

  #1756096

  You have a lot going on here. I'll attempt to fix your code.

  A little CTE magic, a little bit of join, some group by clarification, and voila!:

  with foo as (

  --GET INZET FROM DRP, DAG/AVOND & VAST

  SELECTLOCATIECODE, AfdelingZPT, DATEPART(ISO_WEEK, Begindatum) wk, DienstGroep, FlexVast, OEGroep, sum(cast(DUUR as float))/60 as Uren

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='DAG/AVOND'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  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 dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  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

  -- GET PNIL FLEX

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'PNILFLEX'

  AND M.NiveauZPT = 'PNIL'

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

  -- GET HRS NIET IN DRP

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'NOTINDRP'

  AND M.NiveauZPT = 'NID'

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

  -- GET INLEEN HRS

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'INLEEN'

  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

  -- GET UITLEEN HRS

  UNION ALL

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

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'UITLEEN'

  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

  ),

  fob as (

  SELECTLOCATIECODE, AfdelingZPT, DATEPART(ISO_WEEK, Begindatum) wk, DienstGroep, FlexVast, OEGroep, sum(cast(DUUR as float))/60 as Overig

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='Overig'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  )

  SELECTa.AfdelingZPT

  ,SUM(a.Uren) INZET

  ,SUM(a.Overig)

  FROM foo a

  INNER JOIN fob b

  ON a.LocatieCode = b.LocatieCode

  AND a.AfdelingZPT = b.AfdelingZPT

  GROUP BYa.LocatieCode, a.AfdelingZPT

• JJR333

  Default port

  Points: 1469

  More actions

  October 31, 2014 at 11:04 am

  #1756105

  @sqlslacker: Great, thank you, works like a charm! 🙂

  I find it difficult to choose the right solution, there seem to be many ways to approach things.

  Regards,

  Julian

• JJR333

  Default port

  Points: 1469

  More actions

  October 31, 2014 at 12:32 pm

  #1756134

  It seems I need another solution.

  FOO returns more rows than FOB.

  Thinking of using Temp tables for FOO and FOB, then joining them.

  This does not work, one of the CTE's has "gone", probably.

  SELECT AfdelingZPT, SUM(Uren),

  (SELECT AfdelingZPT, SUM(OVERIG)

  FROM FOB

  WHERE FOO.AfdelingZPT = FOB.AfdelingZPT

  GROUP BY AfdelingZPT ) AS TEST

  FROM FOO

  GROUP BY AfdelingZPT

• sqlslacker

  SSChasing Mays

  Points: 657

  More actions

  October 31, 2014 at 12:37 pm

  #1756135

  There are many ways to approach this. 🙂

  If foo returns more than fob, you could try turning my inner join into a left outer join. That will give you NULL for where there's no record in fob, but it will at least let you see what's missing a match. Maybe you can refine your join criteria (or loosen it up if I did something wrong ;))

• JJR333

  Default port

  Points: 1469

  More actions

  October 31, 2014 at 12:54 pm

  #1756138

  Thanks. I should be able to figure it out.

  A few beers may help (it's evening overhere 😉 )

• JJR333

  Default port

  Points: 1469

  More actions

  November 1, 2014 at 4:58 am

  #1756169

  Solved it using Table variables.

  DECLARE @T TABLE

  (

  AfdelingZPT varchar(3) COLLATE database_default ,

  Uren Float

  ) ;

  DECLARE @TT TABLE

  (

  AfdelingZPT varchar(3) COLLATE database_default ,

  Uren Float

  ) ;

  DECLARE @LOCATIECODE VARCHAR(5),@Jaar int, @Week int

  SET @LocatieCode = 'Ru'

  SET @week = 40

  SET @jaar = 2014

  DECLARE @Flexpool varchar(9)

  -- Assign value to @Flexpool

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

  INSERT INTO @TT (AfdelingZPT, Uren)

  (

  --GET INZET FROM DRP, DAG/AVOND & VAST

  SELECTAfdelingZPT, sum(cast(DUUR as float))/60 as Uren

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='DAG/AVOND'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  UNION ALL

  SELECTM.AfdelingZPT, SUM(M.Hrs) AS Uren

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  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

  --ELSE

  -- GET PNIL FLEX

  UNION ALL

  SELECTM.AfdelingZPT, SUM(M.Hrs) AS Uren

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'PNILFLEX'

  AND M.NiveauZPT = 'PNIL'

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

  -- GET HRS NIET IN DRP

  UNION ALL

  SELECTM.AfdelingZPT, SUM(M.Hrs) AS Uren

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'NOTINDRP'

  AND M.NiveauZPT = 'NID'

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

  -- GET INLEEN HRS

  UNION ALL

  SELECTM.AfdelingZPT, SUM(M.Hrs) AS Uren

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'INLEEN'

  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

  -- GET UITLEEN HRS

  UNION ALL

  SELECTM.AfdelingZPT, -SUM(M.Hrs) AS Uren

  FROMDRPHrsManual M

  WHEREM.LocatieCode = @LocatieCode

  --AND M.AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND M.DienstGroep = 'UITLEEN'

  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

  );

  INSERT INTO @T (AfdelingZPT, Uren)

  (

  SELECTAfdelingZPT, sum(DUUR/60.0) as Uren

  FROMDRPDATA

  WHERELocatieCode = @LocatieCode

  --AND AfdelingZPT = @Afdeling

  AND dbo.isoyear(Begindatum) = @Jaar

  AND DATEPART(ISO_WEEK,Begindatum) = @WEEK

  AND DienstGroep ='Overig'

  AND OEGroep ='ZORG'

  AND FlexVast ='VAST'

  AND NiveauZPT <>'STAGIAIR'

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

  GROUP BY LocatieCode, DATEPART(ISO_WEEK, Begindatum), AfdelingZPT,DienstGroep,FlexVast, OEGroep

  );

  SELECTTT.AfdelingZPT, SUM(TT.Uren) INZET,

  (

  SELECT T.Uren

  FROM @T T

  WHERE TT.AfdelingZPT = T.AfdelingZPT

  ) OVERIG

  FROM@TT TT

  GROUP BY TT.AfdelingZPT;