5
\$\begingroup\$

I have a "litte" problem with my stored procedure because I need some values selected at a point in time.

I need to do many select and group by's in my Left outer join which looks and feels like I am doing something really wrong. It would be very nice if some one could take a look at it.

In this Select there are at least 13 Tables involved:

 -- Add the parameters for the stored procedure here @Wann AS datetime2(7) = null, @EinrichtungId AS int = null, @MitarbeiterId AS int = null AS -- if there was no submitted datetime set it SET @Wann = ISNULL(@Wann,GETDATE()) -- declare the previous month parameter DECLARE @oldDate AS datetime2(7)= null; -- set the previous month SET @oldDate= DATEADD(month, -1, @Wann); SELECT Mitarbeiter.MitarbeiterId, Mitarbeiter.Personalnummer, Mitarbeiter.Vorname, Mitarbeiter.Nachname, Mitarbeiter.IsAktiv, Mitarbeiter.freierMa, Mitarbeiter.IsFuehrungskraft, Mitarbeiter.Eintrittsdatum, Mitarbeiter.Austrittsdatum, Mitarbeiter.Eintrittsurlaub, Mitarbeiter.Austrittsurlaub, Mitarbeiter.RefQualifikationId, MSK.Buchung, MSK.Buchungsdatum, MSK.IstStartBuchung, MU.Jahresurlaub, MU.UrlaubGültigAb, MS.Stunden, MS.StundenGültigAb, ME.RefEinrichtungID, ME.EinrichtungGültigAb, MT.RefTarifvertragId, MT.TarifvertragGültigAb, Nachtrag.NachtragStunden, -- tries to find the last PlanKrank ISNULL(PKTable.PlanKrank,0) AS PlanKrank, -- tries to find the last AusbezahltMonat ISNULL(PL.AktuellAusbezahltMonat,OldPlan.OldAusbezahltMonat) AusbezahltVormonat, CASE WHEN Pl.CurrentStundenKonto IS NOT NULL THEN Pl.CurrentStundenKonto +ISNULL(PL.AktuellAusbezahltMonat,0)*-1 + ISNULL(Nachtrag.NachtragStunden,0)*-1 WHEN OldPlan.OldCurrentStundenKonto IS NOT NULL THEN OldPlan.OldCurrentStundenKonto+ ISNULL(PL.PlanStunden,0) WHEN PL.PlanStunden IS NOT NULL THEN Mitarbeiter.StundenKonto + PL.PlanStunden ELSE Mitarbeiter.StundenKonto END AS StundenKontoVormonat, CASE WHEN OldPlan.OldCurrentStundenKonto IS NOT NULL THEN OldPlan.OldCurrentStundenKonto+ ISNULL(PL.PlanStunden,0) WHEN Pl.CurrentStundenKonto IS NOT NULL THEN Pl.CurrentStundenKonto WHEN PL.PlanStunden IS NOT NULL THEN Mitarbeiter.StundenKonto + PL.PlanStunden ELSE Mitarbeiter.StundenKonto END AS Stundensaldo, ISNULL(JBU.JahresBeginUrlaub, -- if non JahresBeginUrlaub for an open Plan exist -- can still for the current month a JahresBeginUrlaub exist ISNULL(MBU.JahresBeginUrlaub,0)) -- if non Plan was closed try to addd the JahresBeginUrlaub from the previous month + (case when OldPlan.OldCurrentUrlaubskonto IS NULL THEN ISNULL(OJBU.JahresBeginUrlaub,0) else OldPlan.OldCurrentUrlaubskonto END) - ISNULL(PL.PlanUrlaub,0) - ISNULL(Nachtrag.NachtragUrlaub,0) AS Urlaubskonto, -- just to see the value JBU.JahresBeginUrlaub, OldPlan.OldCurrentStundenKonto, OldPlan.OldCurrentUrlaubskonto, PL.PlanUrlaub, PL.PlanStunden, Pl.CurrentStundenKonto, Nachtrag.NachtragUrlaub FROM Mitarbeiter -- agreement on tariffs LEFT OUTER JOIN ( SELECT RefMitarbeiterId, RefTarifvertragId, GültigAb AS TarifvertragGültigAb FROM MitarbeiterTarifvertrag OUTERMT WHERE GültigAb = ( SELECT MAX(GültigAb) FROM MitarbeiterTarifvertrag WHERE RefMitarbeiterId = OUTERMT.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@Wann) AND MONTH(GültigAb) <= MONTH(@Wann) ) OR YEAR(GültigAb) < YEAR(@Wann) ) ) GROUP BY RefMitarbeiterId, RefTarifvertragId, GültigAb ) MT ON Mitarbeiter.MitarbeiterId = MT.RefMitarbeiterId -- facility LEFT OUTER JOIN ( SELECT RefMitarbeiterId, RefEinrichtungID, GültigAb AS EinrichtungGültigAb FROM Mitarbeiter_Einrichtung OUTERME WHERE GültigAb = ( SELECT MAX(GültigAb) FROM Mitarbeiter_Einrichtung WHERE RefMitarbeiterId = OUTERME.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@Wann) AND MONTH(GültigAb) <= MONTH(@Wann) ) OR YEAR(GültigAb) < YEAR(@Wann) ) ) GROUP BY RefMitarbeiterId, RefEinrichtungID, GültigAb ) ME ON Mitarbeiter.MitarbeiterId = ME.RefMitarbeiterId -- holiday LEFT OUTER JOIN ( SELECT RefMitarbeiterId, Urlaub AS Jahresurlaub, GültigAb AS UrlaubGültigAb FROM MitarbeiterUrlaub OUTERMU WHERE GültigAb = ( SELECT MAX(GültigAb) FROM MitarbeiterUrlaub WHERE RefMitarbeiterId = OUTERMU.RefMitarbeiterId AND ( YEAR(GültigAb) = YEAR(@Wann) OR YEAR(GültigAb) < YEAR(@Wann)) ) GROUP BY RefMitarbeiterId, Urlaub, GültigAb ) MU ON Mitarbeiter.MitarbeiterId = MU.RefMitarbeiterId -- work hours LEFT OUTER JOIN ( SELECT RefMitarbeiterId, Stunden, GültigAb AS StundenGültigAb FROM MitarbeiterStunden OUTERMS WHERE GültigAb = ( SELECT MAX(GültigAb) FROM MitarbeiterStunden WHERE RefMitarbeiterId = OUTERMS.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@Wann) AND MONTH(GültigAb) <= MONTH(@Wann) ) OR YEAR(GültigAb) < YEAR(@Wann) ) ) GROUP BY RefMitarbeiterId, Stunden, GültigAb ) MS ON Mitarbeiter.MitarbeiterId = MS.RefMitarbeiterId -- hours account LEFT OUTER JOIN ( SELECT RefMitarbeiterId, Stundensaldo, Buchung, IstStartBuchung, Buchungsdatum FROM MitarbeiterStundenkonto OUTERMSK WHERE Buchungsdatum = ( SELECT MAX(Buchungsdatum) FROM MitarbeiterStundenkonto WHERE RefMitarbeiterId = OUTERMSK.RefMitarbeiterId AND ( ( YEAR(Buchungsdatum) = YEAR(@Wann) AND MONTH(Buchungsdatum) <= MONTH(@Wann) ) OR YEAR(Buchungsdatum) < YEAR(@Wann) ) ) GROUP BY RefMitarbeiterId, Stundensaldo, Buchung, IstStartBuchung, Buchungsdatum ) MSK ON Mitarbeiter.MitarbeiterId = MSK.RefMitarbeiterId -- Plan --all Values from the last closed Plan LEFT OUTER JOIN ( SELECT RefMitarbeiterId, CurrentStundenKonto AS OldCurrentStundenKonto, CurrentUrlaubskonto AS OldCurrentUrlaubskonto, AusbezahltMonat AS OldAusbezahltMonat FROM [Plan] OUTERPLAN WHERE Jahr = ( SELECT MAX(Jahr) FROM [Plan] WHERE Abgeschlossen = 1 AND RefMitarbeiterId = OUTERPLAN.RefMitarbeiterId AND ( ( Jahr = YEAR(@oldDate) AND Monat <= MONTH(@oldDate) ) OR Jahr < YEAR(@oldDate) ) GROUP BY Jahr ) AND Monat = ( SELECT MAX(Monat) FROM [Plan] INNERPLAN WHERE RefMitarbeiterId = OUTERPLAN.RefMitarbeiterId AND Abgeschlossen = 1 AND Jahr = ( SELECT MAX(Jahr) FROM [Plan] WHERE Abgeschlossen = 1 AND RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND ( ( Jahr = YEAR(@oldDate) AND Monat <= MONTH(@oldDate) ) OR Jahr < YEAR(@oldDate) ) GROUP BY Jahr ) AND Monat <= MONTH(@oldDate) ) GROUP BY RefMitarbeiterId, CurrentStundenKonto, CurrentUrlaubskonto, AusbezahltMonat ) OldPlan ON Mitarbeiter.MitarbeiterId = OldPlan.RefMitarbeiterId --all supplement Values LEFT OUTER JOIN ( SELECT RefMitarbeiterId, NachtragStunden, NachtragUrlaub, Abgeschlossen FROM [Plan] OUTERPLAN WHERE PlanId in ( SELECT PlanId FROM [Plan] WHERE Jahr = YEAR(@wann) AND Monat = MONTH(@wann) AND Abgeschlossen = 1 ) GROUP BY RefMitarbeiterId, NachtragStunden, NachtragUrlaub, Abgeschlossen ) Nachtrag ON Mitarbeiter.MitarbeiterId = Nachtrag.RefMitarbeiterId -- Krank Wert des letzten Planes --realized the month crossing Krankberechnung LEFT OUTER JOIN ( SELECT RefMitarbeiterId, PlanKrank FROM [Plan] OUTERPLAN WHERE PlanId = ( SELECT TOP 1 PlanId FROM [Plan] WHERE ( ( Jahr = YEAR(@oldDate) AND Monat <= MONTH(@oldDate) ) OR Jahr < YEAR(@oldDate) ) AND RefMitarbeiterId =OUTERPLAN.RefMitarbeiterId Group by RefMitarbeiterId, PlanKrank, Jahr, Monat, PlanId ORDER BY Jahr DESC, Monat DESC ) GROUP BY RefMitarbeiterId, PlanKrank ) AS PKTable ON Mitarbeiter.MitarbeiterId = PKTable.RefMitarbeiterId -- tries to find the JahresUrlaubes in the current year constellation for the last open (Abgeschlossen = 0) Plan LEFT OUTER JOIN ( SELECT RefMitarbeiterId, JahresBeginUrlaub FROM [Plan] OUTERPLAN WHERE Jahr = YEAR(@Wann) AND JahresBeginUrlaub = ( SELECT MAX(JahresBeginUrlaub) FROM [Plan] WHERE Jahr = YEAR(@Wann) AND RefMitarbeiterId = OUTERPLAN.RefMitarbeiterId AND Abgeschlossen = 0 ) ) AS JBU ON Mitarbeiter.MitarbeiterId = JBU.RefMitarbeiterId -- tries to find the JahresUrlaubes in the current month year constellation LEFT OUTER JOIN ( SELECT RefMitarbeiterId, JahresBeginUrlaub FROM [Plan] OUTERPLAN WHERE Jahr = YEAR(@Wann) AND Monat = MONTH(@Wann) AND JahresBeginUrlaub = ( SELECT MAX(JahresBeginUrlaub) FROM [Plan] WHERE Jahr = YEAR(@Wann) AND Monat = MONTH(@Wann) AND RefMitarbeiterId = OUTERPLAN.RefMitarbeiterId ) GROUP BY RefMitarbeiterId, JahresBeginUrlaub ) AS MBU ON Mitarbeiter.MitarbeiterId = MBU.RefMitarbeiterId --tries to find the JahresUrlaubes for the last open (Abgeschlossen = 0) Plan in the previous year LEFT OUTER JOIN ( SELECT RefMitarbeiterId, JahresBeginUrlaub FROM [Plan] OUTERPLAN WHERE Jahr = YEAR(@Wann) -1 AND JahresBeginUrlaub = ( SELECT MAX(JahresBeginUrlaub) FROM [Plan] WHERE Jahr = YEAR(@Wann) -1 AND RefMitarbeiterId = OUTERPLAN.RefMitarbeiterId AND Abgeschlossen = 0 ) ) AS OJBU ON Mitarbeiter.MitarbeiterId = OJBU.RefMitarbeiterId --sum all values from the last open (Abgeschlossen = 0) Plan LEFT OUTER JOIN ( SELECT RefMitarbeiterId, SUM(PlanStunden) AS PlanStunden, SUM(PlanUrlaub ) AS PlanUrlaub, SUM(AusbezahltMonat ) AS AktuellAusbezahltMonat, SUM(CurrentStundenKonto ) AS CurrentStundenKonto FROM ( SELECT RefMitarbeiterId, PlanStunden, PlanUrlaub , AusbezahltMonat , CurrentStundenKonto FROM [Plan] INNERPLAN WHERE PlanId in ( SELECT TOP (100) PERCENT PlanId FROM [Plan] WHERE ( ( Jahr = YEAR(@oldDate) AND Monat <= MONTH(@oldDate) ) OR Jahr < YEAR(@oldDate) ) AND Abgeschlossen = 0 AND RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND RefEinrichtungId in ( SELECT Einrichtung.EinrichtungId FROM Einrichtung INNER JOIN Einrichtung AS HEO ON Einrichtung.RefEinrichtungId = HEO.EinrichtungId INNER JOIN Mitarbeiter_Einrichtung ON HEO.EinrichtungId = dbo.Mitarbeiter_Einrichtung.RefEinrichtungID WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND GültigAb =( -- SELECT MAX(GültigAb) FROM Mitarbeiter_Einrichtung WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@oldDate) AND MONTH(GültigAb) <= MONTH(@oldDate) ) OR YEAR(GültigAb) < YEAR(@oldDate) ) ) ) Group by RefMitarbeiterId, PlanStunden, PlanUrlaub, AusbezahltMonat , CurrentStundenKonto, Jahr, Monat, PlanId ORDER BY Jahr DESC, Monat DESC ) Group by RefMitarbeiterId, PlanStunden, PlanUrlaub, AusbezahltMonat , CurrentStundenKonto, Jahr, Monat ) OUTERPLAN Group by RefMitarbeiterId )AS PL ON Mitarbeiter.MitarbeiterId = PL.RefMitarbeiterId WHERE ME.RefEinrichtungID = ISNULL(@EinrichtungId,ME.RefEinrichtungID) AND Mitarbeiter.MitarbeiterId = ISNULL(@MitarbeiterId,Mitarbeiter.MitarbeiterId) GROUP BY Mitarbeiter.MitarbeiterId, Mitarbeiter.Personalnummer, Mitarbeiter.Vorname, Mitarbeiter.Nachname, Mitarbeiter.IsAktiv, Mitarbeiter.freierMa, Mitarbeiter.IsFuehrungskraft, Mitarbeiter.StundenKonto, Mitarbeiter.Eintrittsdatum, Mitarbeiter.Austrittsdatum, Mitarbeiter.Eintrittsurlaub, Mitarbeiter.Austrittsurlaub, Mitarbeiter.RefQualifikationId, MSK.Buchung, MSK.Buchungsdatum, MSK.IstStartBuchung, MU.Jahresurlaub, MU.UrlaubGültigAb, MS.Stunden, MS.StundenGültigAb, ME.RefEinrichtungID, ME.EinrichtungGültigAb, MT.RefTarifvertragId, MT.TarifvertragGültigAb, Nachtrag.NachtragStunden, Nachtrag.NachtragUrlaub, PlanKrank, Stundensaldo, Urlaubskonto, PL.AktuellAusbezahltMonat, Pl.CurrentStundenKonto, PL.PlanStunden, PL.PlanUrlaub, OldPlan.OldAusbezahltMonat, OldPlan.OldCurrentStundenKonto, OldPlan.OldCurrentUrlaubskonto, JBU.JahresBeginUrlaub, OJBU.JahresBeginUrlaub, MBU.JahresBeginUrlaub ORDER BY Nachname, Vorname

The result of this mess is one or multiple employee/s with all information for the given time

Edit

Making a note of this comment, subsequent to an answer, so as not to invalidate it:

I translated the comments on my procedure and added a few i hope it is now more clear, if not please ask what ever you may not quiet understand (p.s. sry for my bad english) – WiiMaxx

\$\endgroup\$
4
  • \$\begingroup\$ Any chance you could show an example of what the output would look like? When it comes to SQL, a visual of the data goes a long way to clarifying the purpose of the code. \$\endgroup\$ Commented Dec 8, 2014 at 16:13
  • \$\begingroup\$ I have added the sql-server tag because this SQL looks like SQL-Server syntax. Please correct the tag if it is wrong. \$\endgroup\$ Commented Dec 8, 2014 at 16:24
  • 1
    \$\begingroup\$ This looks like the query could certainly use some optimization... However, being that all the table & column names are in German, would you mind to provide a little more explanation on what your code is doing? \$\endgroup\$ Commented Dec 8, 2014 at 16:39
  • 1
    \$\begingroup\$ @Phrancis Even if one knows German and the schema is sane, it is not clear at all what the goal of this query is! \$\endgroup\$ Commented Dec 8, 2014 at 16:53

2 Answers 2

Reset to default
4
\$\begingroup\$

Regardless of the purpose of the query, I see that it contains a lot of joins involving subselects. Therefore the query would benefit greatly from the use of Common Table Expressions.

\$\endgroup\$
2
\$\begingroup\$

Please keep in mind that I do not speak German, so I used Google Translate. Excuse me if some of the terms are unclear or mistranslated.

Comments

As written, your English comments are not helpful. 

-- Add the parameters for the stored procedure here 


-- declare my intern parameters 


-- set the previous month

Those are all perfectly obvious from looking at the code. What would be more useful would be if you had comments that explained why you are doing something. Your German comments appear more useful, as you use them to label sections of code, mostly.

Consistency

While we're on the topic of language, why do you mix English and German naming and comments? I would say pick one and stick to it. For example, @oldDate would instead be something like @altDatum. For comments, you could also make them bilingual, e.g.:

-- Mitarbeiter_Einrichtung (Employee Revenues)

Your table aliases, while consistent, are not helpful I find. Look at this from the end of your query, for instance:

MSK.Buchung, MSK.Buchungsdatum, MSK.IstStartBuchung, MU.Jahresurlaub, MU.UrlaubGültigAb, MS.Stunden, MS.StundenGültigAb, ME.RefEinrichtungID, ME.EinrichtungGültigAb, MT.RefTarifvertragId, MT.TarifvertragGültigAb,

One cannot tell just by looking at it what all those aliases mean. I have to go back through the whole code to find that MSK means MitarbeiterStundenkonto. That would be a nightmare for a new person to maintain if the code based is all like that.

This is not only inconsistently space, but it is also very cryptic as to not only what you are doing, but also why.

 ISNULL(JBU.JahresBeginUrlaub, ISNULL(MBU.JahresBeginUrlaub,0)) -- wenn kein Plan abgeschlossen wurde wird versucht der JahresBeginUrlaub des Vorjahres zu addieren + (case when OldPlan.OldCurrentUrlaubskonto IS NULL THEN ISNULL(OJBU.JahresBeginUrlaub,0) else OldPlan.OldCurrentUrlaubskonto END) - ISNULL(PL.PlanUrlaub,0) - ISNULL(Nachtrag.NachtragUrlaub,0) AS Urlaubskonto,

ISNULL()

I noticed a lot of ISNULL() checks that could likely be eliminated. In some cases, it doesn't seem to even make sense with what your comments say your code is doing.

 -- tries to find the last PlanKrank ISNULL(PKTable.PlanKrank,0) AS PlanKrank,

What this actually does is replace any NULL value with 0. This would make sense if you are outputting to a process that cannot take NULL as an input, or if a NULL value would mess up calculations. I would try to eliminate some of those that are not needed.

Calculations using CASE WHEN

This operation could use some clarification. Think if a new DBA came in and had to maintain your code. Would that leave them scratching their head?

 CASE WHEN Pl.CurrentStundenKonto IS NOT NULL THEN Pl.CurrentStundenKonto +ISNULL(PL.AktuellAusbezahltMonat,0)*-1 + ISNULL(Nachtrag.NachtragStunden,0)*-1 WHEN OldPlan.OldCurrentStundenKonto IS NOT NULL THEN OldPlan.OldCurrentStundenKonto+ ISNULL(PL.PlanStunden,0) WHEN PL.PlanStunden IS NOT NULL THEN Mitarbeiter.StundenKonto + PL.PlanStunden ELSE Mitarbeiter.StundenKonto END AS StundenKontoVormonat,

What it appears to do is:

  1. If CurrentStundenKonto ("Current hours account"), in other word if there are no hours, you set it to 0.

  2. If AktuellAusbezahltMonat ("Currently Paid out month") is null you also set it to 0, then multiply by -1 which gives you either 0 or a negative value.

  3. If NachtragStunden ("Additional Hours" guessing it is the same as "Overtime") is null you also set it to 0, and multiply by -1 again so you have either 0 or a positive value.

Else...

  1. If Old current hours are not null, you add those up to PlanStunden ("Schedule hours") or 0, which then gives you either 0 or a positive value.

Else...

  1. If Schedule hours are not null, then you add those up or Current hours, which gives you a positive value.

Finally...

  1. If none of the above are true, then you use the value in the Mitarbeiter table.

And all that gives you the hours account for each employee in the last 30 days. Think there is a way to simplify this logic? Let's see. In all cases, but one, you are adding either 0 or positive values. I don't know enough about your schema to offer an actual rewritten code, but hopefully you can work it out. Sometimes breaking things down to plain English (or German) can help you see flaws / potential simplification. Remember, KISS!

The elephant in the room

Remember what I said about descriptive aliases in my other answer? I had to scroll down to line # 397 to find out what the heck PL meant, when I was looking at the above CASE statement.

As @200_success pointed out, you should use CTEs or Common Table Expressions. It's a bit difficult to work your way out of subqueries into CTEs, you need to work your way backwards from the "deepest" subquery up to the "shallowest" or earliest query code.

Here is your code right now, for that section:

LEFT OUTER JOIN ( SELECT RefMitarbeiterId, SUM(PlanStunden) AS PlanStunden, SUM(PlanUrlaub ) AS PlanUrlaub, SUM(AusbezahltMonat ) AS AktuellAusbezahltMonat, SUM(CurrentStundenKonto ) AS CurrentStundenKonto FROM ( SELECT RefMitarbeiterId, PlanStunden, PlanUrlaub , AusbezahltMonat , CurrentStundenKonto FROM [Plan] INNERPLAN WHERE PlanId in ( SELECT TOP (100) PERCENT PlanId FROM [Plan] WHERE ( ( Jahr = YEAR(@oldDate) AND Monat <= MONTH(@oldDate) ) OR Jahr < YEAR(@oldDate) ) AND Abgeschlossen = 0 AND RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND RefEinrichtungId in ( SELECT Einrichtung.EinrichtungId FROM Einrichtung INNER JOIN Einrichtung AS HEO ON Einrichtung.RefEinrichtungId = HEO.EinrichtungId INNER JOIN Mitarbeiter_Einrichtung ON HEO.EinrichtungId = dbo.Mitarbeiter_Einrichtung.RefEinrichtungID WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND GültigAb =( -- SELECT MAX(GültigAb) FROM Mitarbeiter_Einrichtung WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@oldDate) AND MONTH(GültigAb) <= MONTH(@oldDate) ) OR YEAR(GültigAb) < YEAR(@oldDate) ) ) ) Group by RefMitarbeiterId, PlanStunden, PlanUrlaub, AusbezahltMonat , CurrentStundenKonto, Jahr, Monat, PlanId ORDER BY Jahr DESC, Monat DESC ) Group by RefMitarbeiterId, PlanStunden, PlanUrlaub, AusbezahltMonat , CurrentStundenKonto, Jahr, Monat ) OUTERPLAN Group by RefMitarbeiterId )AS PL ON Mitarbeiter.MitarbeiterId = PL.RefMitarbeiterId

I'm not sure how familiar you are with CTEs, but they help readability a ton, by declaring your subqueries right at the beginning, and you can set meaningful names to reference them later in the script. Extremely handy and much easier to maintain. Now, working your way back from subqueries to CTEs can be challenging, but not impossible. A good approach is to work your way backwards, as I said.

Here is how I would start:

 AND GültigAb =( SELECT MAX(GültigAb) FROM Mitarbeiter_Einrichtung WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@oldDate) AND MONTH(GültigAb) <= MONTH(@oldDate) ) OR YEAR(GültigAb) < YEAR(@oldDate) ) )

You could instead write, at the beginning of your query:

WITH cte_MaxGültigAb AS ( SELECT MAX(GültigAb) FROM Mitarbeiter_Einrichtung WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND ( ( YEAR(GültigAb) = YEAR(@oldDate) AND MONTH(GültigAb) <= MONTH(@oldDate) ) OR YEAR(GültigAb) < YEAR(@oldDate) ) )

Then, it is much simpler to reference that CTE, as such:

WHERE RefMitarbeiterId = INNERPLAN.RefMitarbeiterId AND GültigAb IN cte_MaxGültigAb

I prefer the cte prefix personally, but it really can be named anything, including no prefix. Then just work your way up to the next subquery, and combine CTEs together.

NOTE: This will likely not improve performance much, but it certainly will improve readability, which in turn makes it more clear where performance issues are.

PS: You would do yourself a favor if you used smaller indentation space, like 4 or 2 spaces. Horizontal white space makes things more difficult to read.

\$\endgroup\$
2
  • \$\begingroup\$ sry for the late response, i will take a closer look at the CTE \$\endgroup\$ Commented Dec 15, 2014 at 10:36
  • \$\begingroup\$ OK let me know if you have questions. Also feel welcome to post a follow-up question once you have cleaned it up! \$\endgroup\$ Commented Dec 15, 2014 at 14:54

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.