Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Datum_bis berechnen, wenn nur Datum_von vorhanden

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Joe1968, 15 November 2014.

  1. Joe1968

    Joe1968 Benutzer

    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
     
  2. Hony%

    Hony% Datenbank-Guru

    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
    
    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: 15 November 2014
  3. Joe1968

    Joe1968 Benutzer

    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?
     
  4. Hony%

    Hony% Datenbank-Guru

    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: 15 November 2014
  5. Joe1968

    Joe1968 Benutzer

    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?
     
  6. Hony%

    Hony% Datenbank-Guru

    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
    
     
    Walter gefällt das.
  7. Joe1968

    Joe1968 Benutzer

    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 ☺
     
  8. Hony%

    Hony% Datenbank-Guru

    Für viele Probleme können beide eingesetzt werden. Allerdings gibt es Unterschiede. Eine gespeicherte Prozedur kann, soweit ich weiß, nicht wie eine Tabelle benutzt werden und würde damit für dieses Problem ausfallen.

    Gern doch
     
  9. Joe1968

    Joe1968 Benutzer

    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 :-(
     
  10. Hony%

    Hony% Datenbank-Guru

    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: 15 November 2014
  11. Joe1968

    Joe1968 Benutzer

    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 :)
     
  12. Hony%

    Hony% Datenbank-Guru

    Die Funktion finde ich ehrlich gesagt auch eleganter. ;-)
     
  13. pathomorph

    pathomorph Benutzer

    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
     
  14. Hony%

    Hony% Datenbank-Guru

    @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.
     
  15. pathomorph

    pathomorph Benutzer

    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.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden