Datum_bis berechnen, wenn nur Datum_von vorhanden

J

Joe1968

Benutzer
Beiträge
15
Hallo Experten,
ich baue gerade eine Personaldatenbank auf und stehe vor einem Problem. Ich habe eine Tabelle 'tbl_Mitarbeiter', in der die Stammdaten des Mitarbeiters stehen, vereinfachen wir das auf

- Nachname
- Vorname
- Personalnummer

Dann habe ich u.a. eine Tabelle 'tbl_Kostenstellen', Felder sind:
- Personalnummer als Fremdschlüssel
- Datum_von
- Kostenstelle

In der Tabelle tbl_Mitarbeiter gibt es z.B. einen Datensatz (Schmidt, Helmut, 4711).
In der Tabelle tbl_Kostenstellen zu diesem Mitarbeiter drei Einträge
- 4711, 01.01.2010, Personal
- 4711, 01.01.2012, Produktion
- 4711, 01.01.2014, Leitung

Ich möchte mir über eine Abfrage den Namen und die Kostenstelle zu einem beliebigen Datum ausgeben lassen. Ich hatte mir gedacht, es ist vielleicht professioneller, nur das Veränderungsdatum in der Datenbank zu speichern, das Datum_bis aber nicht. Ich wollte damit logische Fehler umgehen. Allerdings bekomme ich es nun nicht hin, mir mit einer Abfrage ausgeben zu lassen zu welcher Kostenstelle der Mitarbeiter z.B. am 15.01.2012 gehörte.

Habt Ihr eine Idee?

Viele Grüße
Joe
 
Werbung:
Hi.

Als einfachen Ansatz würde ich das vorschlagen:
Code: 
SELECT TOP(1) PersNr, Vorname, Nachname, Kostenstelle
FROM pers AS P
NATURAL JOIN kostenstelle AS K
WHERE datum <= '2012-01-15'
ORDER BY datum DESC

Joe1968 schrieb:
Ich hatte mir gedacht, es ist vielleicht professioneller, nur das Veränderungsdatum in der Datenbank zu speichern, das Datum_bis aber nicht. Ich wollte damit logische Fehler umgehen.
Zum Vergrößern anklicken....
Das ist schon richtig so wenn der Übergang nahtlos ist. Letztendlich lässt sich der Zeitraum berechnen und hat damit nichts in der Datenbank verloren.

Gruß
Hony
 
Zuletzt bearbeitet:
Bin gerade unterwegs und kann es nicht ausprobieren. Aber könnte es nicht sein, dass er bei mehreren jüngeren Datensätzen den falschen, also einen früheren auswählt?
 
Die Datensätze werden durch ORDER BY absteigend nach dem Datum sortiert. Durch TOP(1) wird dann der erste Datensatz ausgewählt. Wenn MS-SQL richtig sortieren kann und TOP funktioniert sollte der Fall nicht eintreten.

Edit:

Wasserdicht dürfte das hier sein:
Code: 
SELECT P.PersNr, P.Vorname, P.Nachname, K.Kostenstelle
FROM pers AS P
NATURAL JOIN kostenstelle AS K
INNER JOIN (
  SELECT PersNr, MAX(datum) as datum
  FROM kostenstelle
  WHERE datum <= '2012-01-15'
  GROUP BY PersNr
) AS F
ON  K.datum = F.datum
AND K.PersNr =  F.PersNr

Allerdings habe ich meine Zweifel, dass dieses Monstrum mehr als ein besseres Bauchgefühlt bringt. ;)
 
Zuletzt bearbeitet:
Ok, klingt super. Ich hatte an sowas selber auch schon gedacht, aber wegen einem Folgeproblem verworfen. Und zwar brauche ich diese Anfrage recht häufig, da ich für einen Einsatzplan eine Liste von Namen ausgeben möchte, die zu einem bestimmten Zeitpunkt in einem bestimmten Bereich arbeiten. Somit wollte ich mir einen View schreiben, dem ich noch das Datum mitteile. Aber leider gibt es in Views keinen Order By. Kann man das Problem auch irgendwie lösen?
 
Joe1968 schrieb:
Somit wollte ich mir einen View schreiben, dem ich noch das Datum mitteile.
Zum Vergrößern anklicken....
Geht das überhaupt mit einer View?

Ich würde einfach eine Funktion nutzen:
Code: 
CREATE FUNCTION PersList (@Datum DATE)
RETURNS @PersList TABLE
   (
   PersNr  int,
   Vorname  nvarchar(20),
   Nachname  nvarchar(20),
   Kostenstelle  nvarchar(20)
   )
AS
BEGIN
   INSERT @PersList
     SELECT TOP(1) P.PersNr, P.Vorname, P.Nachname, K.Kostenstelle
     FROM pers AS P
     INNER JOIN kostenstelle AS K
      ON K.PersNr = P.PersNr
     WHERE datum <= @Datum
     ORDER BY datum DESC
   RETURN
END
Code: 
SELECT *
FROM PersList('2012-01-15')
Code: 
PersNr      Vorname              Nachname             Kostenstelle
----------- -------------------- -------------------- --------------------
4711        Helmut               Schmitdt             Produktion
 
Das klingt klasse. Leider habe ich mich noch nicht an Funktionen und gespeicherte Prozeduren gewagt (wahrscheinlich das gleiche?). Das sieht aber erst mal sehr verständlich aus. Wird heute abend gleich ausprobiert.
Vielen Dank für Deine Hilfe ☺
 
So, bin jetzt zum Ausprobieren gekommen. Funktioniert solange es nur einen Namen in der Mitarbeitertabelle gibt. Es soll aber der zum Datum passende Datensatz eines jeden Mitarbeiters ausgegeben werden. Das Problem ist sicherlich das TOP 1, das begrenzt die komplette Ausgabe auf 1.
Ich habe schon versucht, das ganze umzubauen, bin aber nicht weiter gekommen. Ich wollte auch nicht gleich losplärren, dass es nicht geht, sondern selbst erst mal mein Hirn anstrengen. Leider ohne Erfolg :-(
 
Stimmt. Logischer Fehler…

Das Problem lässt sich auf zwei Arten lösen:
Code: 
CREATE FUNCTION PersList (@Datum DATE)
RETURNS @PersList TABLE
   (
   PersNr int,
   Vorname nvarchar(20),
   Nachname nvarchar(20),
   Kostenstelle nvarchar(20)
   )
AS
BEGIN
   INSERT @PersList
     SELECT P.PersNr, P.Vorname, P.Nachname, K.Kostenstelle
     FROM pers AS P
     INNER JOIN kostenstelle AS K
      ON P.PersNr = K.PersNr
     INNER JOIN (
       SELECT PersNr, MAX(datum) as datum
       FROM kostenstelle
       WHERE datum <= @Datum
       GROUP BY PersNr
     ) AS F
     ON K.datum = F.datum
     AND K.PersNr = F.PersNr
   RETURN
END
Also einfach die Alternative von oben.

Oder doch mit einer VIEW:
Code: 
CREATE VIEW PersView AS
SELECT P.PersNr, P.Nachname, P.Vorname, C.von AS datum_von,
CASE
  WHEN C.bis IS NOT NULL THEN DATEADD(dd, -1, C.bis)
  ELSE GETDATE()
END AS datum_bis, C.kostenstelle
FROM pers AS P
INNER JOIN (
   SELECT A.PersNr, A.Datum AS von, MIN(B.datum) AS bis,  A.kostenstelle
   FROM kostenstelle AS A
   LEFT JOIN kostenstelle AS B
   ON A.persnr = B.persnr
   AND A.datum < B.datum
   GROUP BY A.persnr, A.datum, A.kostenstelle
) AS C
ON P.PersNr = C.PersNr

Code: 
SELECT *
FROM PersView
WHERE CAST('2012-01-15' AS DATE) BETWEEN datum_von AND datum_bis

Bei der VIEW in dieser Form kann es allerdings zu seltsamen Werten kommen wenn zukünftige Änderungen in der Datenbank eingepflegt werden. In dem Fall sollte die Abfrage so aussehen:
Code: 
SELECT *
FROM PersView
WHERE CAST('2012-01-15' AS DATE) > = datum_von
AND CAST('2012-01-15' AS DATE) < = datum_bis
 
Zuletzt bearbeitet:
Genau. Ich war gerade dabei, so ziemlich den gleichen Code zu schreiben. Ich hab's auch herausbekommen. Die Variante mit der Funktion finde ich deutlich besser. Ich habe sie noch so abgewandelt, dass ich den Bereich zusätzlich als Variable übergebe. Somit bekomme ich die Liste mit dem entsprechenden Bereich zurück.

Besten Dank für Deine Mühe, Du hast mich auf die richtige Spur gebracht :)
 
Mal was anders:

Es ist nicht sinnvoll die PersonalNummer als PK zu nehmen:
1. Manche Mitarbeiter habe keine (Schüler, Praktikanten, Leiharbeiter...)
2. Diese kann sich auch mal ändern..

Lieber eine allgemeine ID nehmen
 
@pathomorph
Worin unterscheidet sich deiner Meinung nach eine beliebig zugeteilte Personalnummer von einer ebenso beliebig zugeteilten allgemeinen ID? Beides sind künstliche Schlüssel deren einziger Daseinszweck der Verwaltung dient.
 
Werbung:
Deren Bedeutung..

Was ist, wenn der Mitarbeiter keine PersNr hat?! Eine ID muss er dann haben...
Wenn sich die Personalnummer ändert (durch Wechsel der Lohnabrechnung, Rechenzentrum, Umstrukturierung, Übernahmen), dann muss man darauf achten, dass alle Referenzen aktualisiert werden.
Das ist schwieriger zu handeln, als wenn man unabhängig von den Attributen eine ID bestimmt.
 
Zurück
Oben