Selecting Employee details from a complicated schema

Question

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

Answer 1

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.

Answer 2

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.