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