Ankündigung

Einklappen
Keine Ankündigung bisher.

SQL-Abfrage gestalten (Anfänger)

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

    SQL-Abfrage gestalten (Anfänger)

    Hallo,

    ich bin SQL-Anfänger und soll aber in der Firma eine kleine Abfrage basteln. Bis heute hat meine Abfrage auch bestens funktioniert. Nun ist jedoch leider ein Kriterium dazugekommen, das mich etwas aus der Bahn wirft.

    Kurz zum Aufbau der Datenbank, die aus zwei Tabellen besteht:






    Die bisherige Abfrage liefert mir für ein vorgegebenes Projekt nun die Anzahl der Arbeitsstunden pro Mitarbeiter, die Abfrage dazu:

    Spoiler: 
    sSQLCmd = „SELECT z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname, sum(datediff(n, Startzeit, EndZeit)) FROM Zeiten LEFT JOIN Mitarbeiter m ON z.ScannerID=m.ScannerID WHERE z.Projekt=500 GROUP BY z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname“


    Als neues Kriterium sind nun die Spalten „StartDatum“ und „EndDatum“ in der Tabelle Mitarbeiter hinzugekommen. Hintergrund ist, dass die Scanner mit denen sich die Leute einloggen, evtl. weitergegeben werden. Durch die Zuweisung der Zeit erhoffe ich mir, die Daten besser verarbeiten zu können.

    Ich möchte nun also dieselbe Abfrage wie oben umsetzen, allerdings in einem bestimmten Zeitraum. Und für diesen Zeitraum dann auch nur die Arbeiten den Personen zuweisen die den Scanner auch benutzt haben.

    Fall A:
    So möchte ich, wenn ich das Projekt 500 für den Zeitraum vom 01.11.2015 bis 05.11.2015 Abfrage als Ergebnis haben, dass Max Mustermann 1 Stunde an dem Projekt gearbeitet hat.
    Fall B:
    Wenn ich das Projekt 500 für den Zeitraum vom 06.11.2015 bis 12.11.2015 Abfrage, soll als Ergebnis herauskommen, dass Lisa Musterfrau 4 Stunden an dem Projekt gearbeitet hat.
    Fall C:
    Wenn ich das Projekt 500 für den Zeitraum vom 01.11.2015 bis 16.11.2015 Abfrage, soll als Ergebnis herauskommen, dass Lisa Musterfrau 4 Stunden an dem Projekt gearbeitet hat und Max Mustermann 1 Stunde.

    Dazu habe ich nun versucht folgende Abfrage auszuführen:
    Spoiler: 

    sSQLCmd = „SELECT z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname, sum(datediff(n, Startzeit, EndZeit)) FROM Zeiten LEFT JOIN Mitarbeiter m ON z.ScannerID=m.ScannerID WHERE z.Projekt=500 AND z.Datum BETWEEN ’01.11.2015‘ AND ’05.11.2015‘ GROUP BY z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname“

    Aber ich weiß nicht, wie ich nur den Bereich abdecke und ausgebe der auch zur Scannerzuweisung passt, da ich nur die Summe der Projektstunden haben will, die auch zur Zeitspanne in der Scannerbelegung passt.

    Kann mir jemand n Tipp geben bzw. helfen?

    Danke!

    Gruß!

    #2
    WHERE z.Projekt=500

    sollte das nich z.ScannerID sein bzw. ne Projektnummer dies gibt? Generell wärs vielleicht nich schlecht die Tabellen erstmal zu normalisieren.

    Edit:

    Die Verknüpfung der Tabellen über die ScannerID und Left Join versteh ich nich ganz. Das würde bedeuten, jede ScannerID der einen Tabelle bekommt jeweils zwei ScannerID Einträge der anderen bzw. jeder Max und Lisa haben je 5 Stunden damit gearbeitet.

    So 100% steig ich nich dahinter aber ich würd die Spalte ScannerID bei Mitarbeiter raushauen (Was wenn Max beim nächsten mal die 700 bekommt?), bei den Zeiten eine Spalte für die MitarbeiterID hinzufügen und das erstmal darüber verknüpfen.
    Start und EndDatum sollte auch nich zu Mitarbeiter, was wenn Max jetzt wieder scannt?
    Bei Zeiten kann die Spalte Dauer weg, lässt sich ja berechnen und ich würd Datum mit Zeit zusammen in eine Spalte machen.


    Kommentar


      #3
      An diesem Beispiel merke ich wieder warum ich snake case liebe :D

      Kommentar


        #4
        Zitat von katsching
        WHERE z.Projekt=500

        sollte das nich z.ScannerID sein bzw. ne Projektnummer dies gibt? Generell wärs vielleicht nich schlecht die Tabellen erstmal zu normalisieren.

        Edit:

        Die Verknüpfung der Tabellen über die ScannerID und Left Join versteh ich nich ganz. Das würde bedeuten, jede ScannerID der einen Tabelle bekommt jeweils zwei ScannerID Einträge der anderen bzw. jeder Max und Lisa haben je 5 Stunden damit gearbeitet.

        So 100% steig ich nich dahinter aber ich würd die Spalte ScannerID bei Mitarbeiter raushauen (Was wenn Max beim nächsten mal die 700 bekommt?), bei den Zeiten eine Spalte für die MitarbeiterID hinzufügen und das erstmal darüber verknüpfen.
        Start und EndDatum sollte auch nich zu Mitarbeiter, was wenn Max jetzt wieder scannt?
        Bei Zeiten kann die Spalte Dauer weg, lässt sich ja berechnen und ich würd Datum mit Zeit zusammen in eine Spalte machen.
        Hi,

        danke für die Anregunng.
        Ich habe leider kein Plan von SQL und habe das alles nach bestem Wissen und Gewissen gemacht. Von Normalisierung habe ich noch nichts gehört. (Typischer Fall von, können Sie nicht mal eben sowas machen? - Zack Chef wieder weg)

        Durch das LEFT JOIN möchte ich die ScannerID durch den Vornamen und Nachnamen ersetzen, da sich unter der ScannerID niemand etwas vorstellen kann. Darum habe ich die ScannerID in beiden Tabellen eingefügt. So kann ich über die ScannerID in der Zeiten-Tabelle die Person aus der Mitarbeiter-Tabelle abfragen.
        Fängt ein neuer Mitarbeiter an, wird ihm ein Scanner ausgehändigt und die Zuweisung in die Mitarbeitertabelle eingetragen. Hört ein Mitarbeiter auf, wird das Enddtaum in seinen Datensatz geschrieben und der Scanner an einen neuen Mitarbeiter weitergegeben.
        Fragt man anschließend alte Projekte ab, soll aber der "alte" Mitarbeiter mit seiner Arbeitszeit gelistet werden und nicht der "Nachfolger" der denselben Scanner benutzt.
        Die Zuordnung der Scanner ist somit jeweils fix. Also Max hat für den Zeitraum von 1.11.2015 bis 5.11.2015 immer denselben Scanner. Im Idealfall haben sie ihr gesamtes Leben denselben Scanner :).

        Das mit der Dauer stimmt. Das könnte ich noch machen. Das hatte ich damals aus anderen Gründen versucht.

        Ist mein Beispiel über den Fall A bis C klar geworden, oder soll ich das nochmal ausführlicher beschreiben?

        Danke dir!

        Gruß!

        Kommentar


          #5
          mach mal eine tabelle mitarbeiter, eine projekt und eine scanner. danach ergibt sich alles von alleine und du bist fliexibler wenn irgendeiner dieser elemente dazu oder wegkommt

          mag sein, dass ich da was falsch deute aber jetzt könnten doch beliebig viele mitarbeiter zur gleichen zeit den selben scanner im selben projekt besitzen bzw ein scanner kann zeitgleich von verschiedenen personen in verschiedenen projekten sein

          Kommentar


            #6
            Moin,

            wie soll ich dann die einzelnen Verknüpfungen unteinander beziehen?

            Eigl. würde ich die Struktur gerne beibehalten. Die Abfrage ist quasi das letzte was mir fehlt.

            Kommentar


              #7
              wie Michael Laudrup sagt, deine Tabellenstruktur ist nicht gut.

              Mitarbeiter in eigene Tabelle mit einer Personalnummer als eindeutiger Schlüssel.
              Das gleiche gilt für Projekte

              Eine Mappingtabelle zb. MitarbeiterProjektZeiten

              Personal- und Mitarbeiternummer als Schlüssel + die restlichen Spalten wie Startzeit Endzeit usw

              Am Besten liest du dich in Normalformen ein.

              Was hat die ScannerId für eine Bedeutung?

              Select ScannerID, Nachname, Vorname, sum(dauer) From Mitarbeiter m inner join MitarbeiterProjektZeiten mpz on m.personalnummer = mpz.personalnummer inner join Projekt p on p.projektnummer = mpz.projektnummer
              Where Zeitraum between 2342342342342 and projektnummer = 500

              Kommentar


                #8
                Zitat von maaanuuueeel

                Was hat die ScannerId für eine Bedeutung?
                Ich würde schätzen, dass das die eindeutige Nummer des Scanners ist. Wie z.B. eine Seriennummer oder Inventarnummer.
                Aber wie oben schon mehrfach gesagt: Die Tabellen sind schlecht aufgebaut, damit wist du nicht weiterkommen.

                Kommentar


                  #9
                  Zitat von knipser
                  Eigl. würde ich die Struktur gerne beibehalten. Die Abfrage ist quasi das letzte was mir fehlt.
                  Die Struktur macht aber einfach keinen Sinn.

                  Zitat von knipser
                  Die bisherige Abfrage liefert mir für ein vorgegebenes Projekt nun die Anzahl der Arbeitsstunden pro Mitarbeiter, die Abfrage dazu:

                  sSQLCmd = „SELECT z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname, sum(datediff(n, Startzeit, EndZeit)) FROM Zeiten LEFT JOIN Mitarbeiter m ON z.ScannerID=m.ScannerID WHERE z.Projekt=500 GROUP BY z.ScannerID, m.Nachname + ‘, ‘ + m.Vorname“
                  Nein, deine bisher Abfrage ist schon Murks und liefert Müll ;)

                  Hab sie genauso nachgebaut und durch fehlende Verknüpfungen liefert sie mir folgendes:
                  Lisa Musterfrau - 300
                  Max Mustermann - 300

                  SQL:
                  SELECT [Vorname] & " " & [Nachname] AS Name, Zeiten.ScannerID, Sum(DateDiff("n",[Zeiten]![StartZeit],[Zeiten]![EndZeit])) AS DauerMin
                  FROM Zeiten LEFT JOIN Mitarbeiter ON Zeiten.ScannerID = Mitarbeiter.ScannerID
                  WHERE ((Zeiten.Projekt)="1234")
                  GROUP BY [Vorname] & " " & [Nachname], Zeiten.ScannerID, Zeiten.Projekt;
                  Was soll auch anderes rauskommen, dir fehlen Keys ...

                  Kommentar


                    #10
                    Hallo,

                    bevor die Spalten StartDatum und EndDatum dazugekommen sind, war in der Tabelle Mitarbeiter die ScannerID einmalig. Dann konnte ich mit:

                    "SELECT z.ScannerID,m.Nachname + ', ' + m.Vorname,m.Taetigkeit,sum(datediff(n, StartZeit, EndZeit)) as Zeit FROM Zeiten z LEFT JOIN Mitarbeiter m ON z.ScannerID=m.ScannerID WHERE Projekt=111111 GROUP BY z.ScannerID,m.Nachname + ', ' + m.Vorname,m.Taetigkeit"
                    abfragen welcher Scanner (Mitarbeiter) wie viele Stunden an einem Projekt gearbeitet hat. Die Abfrage funktioniert auch wunderbar.

                    Seitdem nun die ScannerID in der Tabelle Mitarbeiter mehrfach vorkommen kann (jeweils in einem anderen Zeitraum) habe ich jedoch Probleme die Abfrage korrekt zu gestalten :D Da funktioniert das Ganze nicht mehr.

                    Wie gesagt ich habe leider kein Plan von SQL. Wie sollte ich die Tabellen aufbauen? Der Rest funktioniert einfach super, daher würde ich ungerne alles umwerfen. Wenn es aber nicht anders geht, muss ich wohl mit Keys (?) und der Normalisierung arbeiten. Ich versuche mich mal einzulesen.

                    ScannerID ist, wie slayer beschrieben hat, eine eindeutige Zuordnung eines Barcode-Scanners an eine bestimmte Person.

                    Danke bisher!

                    Kommentar


                      #11
                      So, hier mal eine Lösung für dich.

                      Weiterhin 2 Tabellen, Mitarbeiter und Zeiten. Projekt und Scanner könnte man auch noch machen, brauch man jetzt aber nicht.

                      Jeder Mitarbeiter hat eine eigene ID und diese wird später in Zeiten verknüpft.

                      Mitarbeiter


                      Zeiten


                      Jetzt die Abfrage über beide Tabellen erstellen.


                      Et voilà


                      SQL:
                      SELECT [Mitarbeiter.Nachname] & ", " & [Mitarbeiter.Vorname] AS Name, Sum(DateDiff("n",[Zeiten]![StartZeit],[Zeiten]![EndZeit])) AS Dauer
                      FROM Mitarbeiter INNER JOIN Zeiten ON Mitarbeiter.MitarbeiterID = Zeiten.MitarbeiterID
                      WHERE (((Zeiten.Datum) Between #11/1/2015# And #11/7/2015#) AND ((Zeiten.Projekt)="1234"))
                      GROUP BY [Mitarbeiter.Nachname] & ", " & [Mitarbeiter.Vorname];
                      Zitat von knipser
                      Seitdem nun die ScannerID in der Tabelle Mitarbeiter mehrfach vorkommen kann (jeweils in einem anderen Zeitraum) habe ich jedoch Probleme die Abfrage korrekt zu gestalten :D Da funktioniert das Ganze nicht mehr.
                      Genau da liegt halt das Problem mit der Struktur.

                      Zitat von knipser
                      ScannerID ist, wie slayer beschrieben hat, eine eindeutige Zuordnung eines Barcode-Scanners an eine bestimmte Person.
                      Ist es eben nicht. Es ist eine Zuordnung zu unterschiedlichen Mitarbeitern, die nur durch das zusätzliche Datum passt, was aber nicht verknüpft ist / werden kann.

                      Kommentar


                        #12
                        Danke dir!
                        Hm, stimmt. Mit der Datumszuweisung ist es nicht mehr eindeutig.

                        Also meine erste Spalte ist meine eindeutige ID, die kann ich verwenden.

                        Aber woher nimmst du nun die Zuweisung in Zeiten, dass MitarbeiterID 1 zu dem Scanner 500 gehört. Diese Zuweisung muss ich ja irgendwoher nehmen.

                        Ich bekomme vom Barcodescanner nur die ScannerID, die Scanzeit sowie das Datum. Ich muss dann ja noch wissen, welche MitarbeiterID zu dem Scanner gehört. D.h. im Fall für Scanner 500 muss irgendwo der Bezug MitarbeiderID 1 Scanner 500 herkommen, oder?

                        Danke dir soweit meega. Ich hoffe ich kann dir auch irgendwann mal helfen :D

                        Gruß!

                        //Edit:
                        Muss das dann nicht wieder eine extra Tabelle sein mit den Spalten:

                        ID | Scanner | Mitarbeiter



                        Kommentar


                          #13
                          Dann verschieb den Scanner von Zeiten in Mitarbeiter. Dann muss aber sichergestellt sein, dass nur Mitarbeiter 1 während StartDatum und EndDatum den Scanner hat.

                          Kommentar


                            #14
                            Jo, das wollte ich eh überprüfen bzw. abfangen.

                            Ich danke dir!

                            Ich setz mich mal ran.

                            Gruß!

                            Kommentar


                              #15
                              Die Abfrage für den Mitarbeiter würde dann einfach so aussehen:

                              SELECT Mitarbeiter.MitarbeiterID
                              FROM Mitarbeiter
                              WHERE (((Mitarbeiter.Scanner)=500) AND ((#11/10/2015#) Between [Mitarbeiter].[StartDatum] And [Mitarbeiter].[EndDatum]));

                              Kommentar

                              Lädt...
                              X