Daten per SQL gruppieren

[norwegen60]

Hallo zusammen,

ich habe folgende Daten

Code:

SerNr       Typ         Status
F05323690   MLC10U100   S
F05323691   MLC10U100   S
F05323692   MLC10U100   R
F05323693   MLC10U100   S
F05323694   MLC10U100   S

und möchte sie so gruppieren, dass ich folgende Ausgabe bekommen

Code:

Min(SerNr)   Typ         Status   Anzahl
F05323690    MLC10U100   S        2
F05323692    MLC10U100   R        1 
F05323693    MLC10U100   S        2

Mit einer einfachen

Delphi-Quellcode:

SELECT min(SerNr), Typ, Status, count(SerNr)
FROM Ser_nr
WHERE SerNr BETWEEN 'F05323690' AND 'F05323695'
group by Typ, Status
order by min(SerNr)

sähe das Ergebnis so aus

Code:

Min(SerNr)   Typ         Status   Anzahl
F05323690    MLC10U100   S        4
F05323692    MLC10U100   R        1

Hintergrund ist, dass ein Typenschldprogramm die Daten so übergeben bekommt.
Im 1. Fall würde es die Serien-Nr.

• F05323690 bis F05323691 mit Status S, (Anzahl 2 = 2 fortlaufende Serien-Nr.)
• F05323692 mit Status R
• F05323693 bis F05323694 wieder mit Status S

drucken
Im 2. - falschen Fall - würde es die Serien-Nr.

• F05323690 bis F05323693 mit Status S, (Anzahl 4 = 4 fortlaufende Serien-Nr.)
• F05323692 mit Status R

drucken. d.h. F05323694 wird gar nicht gedruckt und F05323692 einmal mit Status S und einmal mit Status R

Hat einer einen Tip
Danke
Gerd

[p80286]

Zitat von norwegen60:

d.h. F05323694 wird gar nicht gedruckt und F05323692 einmal mit Status S und einmal mit Status R

Das verstehe ich nicht!
Sowohl in der gewünschten als auch in der falschen Ausgabe taucht die F05323694 nicht auf, und wird darum auch nicht gedruckt.

Gruß
K-H

[jfheins]

Ich glaube, der Drucker bekommt eine Seriennummer und eine Anzahl und zählt die Seriennummer dann automatisch hoch beim Drucken.

Zum Problem: In SQL ist die Reihenfolge der Zeilen nicht definiert. Dein Tool zeigt dir das zwar meistens reproduzierbar nett an, aber du darfst dich darauf nicht verlassen, dass das reproduzierbar ist.
Demzufolge kannst du per group-by auch nicht nur "aufeinanderfolgende Zeilen" gruppieren.

Lösung wäre eine extra Spalte, in die man für (aufeinanderfolgende Seriennummern/gleicher Typ/gleicher Status) Kombinationen die gleiche Zahl rein schreibt. Dann einfach nur nach dieser Spalte gruppieren. Oder du holst dir alle Datensätze nach Seriennummer sortiert und machst diesen Schritt im SQL Client.

[norwegen60]

Zitat von jfheins:

Ich glaube, der Drucker bekommt eine Seriennummer und eine Anzahl und zählt die Seriennummer dann automatisch hoch beim Drucken.

Stimmt. Über Anzahl erfährt das Typenschildprogramm wie viele fortlaufenden Nr. gedruckt werden sollen

"Mein Tool" das die Daten anzeigt ist schon ein SQL und was mir fehlt ist ein Kommando das erkennt wenn innerhalb einer Gruppe ein "schwarzes Schaf" ist das die Gruppe unterbricht.
In der Realität gibt es auch die Serien-Nr. F05323692 mit Status S in der DB. Viele Datensätze später gibt es dieselbe Serien-Nr. aber auch mit Status R und über das Unter-SQL suche ich zuerst mal den letzten und damit aktuelle gültigen Eintrag zu einer Serien-Nr.
Das SQL für die die erste Tabelle sieht in Wirklichkeit so aus

Code:

SELECT s.sernr, s.Typ, s.[Status]
FROM Ser_nr s
INNER JOIN (SELECT S1.SerNr, Max(S1.ID) AS MaxID
            FROM Ser_Nr AS S1
            WHERE s1.SerNr BETWEEN 'F05323690' AND 'F05323694'
            GROUP BY S1.SerNr) AS S3
         ON s.ID = S3.MaxID
order by s.SerNr

[jfheins]

Also eine Lösung wäre ja auch, das Feature mit dem automatischen Hochzählen nicht zu benutzen und alle Datensätze einzeln zum Drucker zu schicken.

[himitsu]

Joar, also entweder eine WindowFnction schreiben, die bei Unterschied um 1 hoch zählt

das alles manuell in einer Stored Proc lösen

oder erstmal im Hauptselect die Ersten raussuchen (Alles, wo direkt vor sich etwas Unterschiedliches liegt
und dann z.B. in einem rekursiven Select alles suchen, wo davor etwas Gleiches liegt und das an den er jeweils ersten Datensatz dran joinen.

oder es alt Clientseitig machen

[norwegen60]

Zitat von jfheins:

Also eine Lösung wäre ja auch, das Feature mit dem automatischen Hochzählen nicht zu benutzen und alle Datensätze einzeln zum Drucker zu schicken.

So hatte ich es auch schon ausprobiert.

Nachteil:
Die Typenschilder sind auf Endlosmaterial und werden vom Drucker geschnitten.
Erzeugt man für jeden Nr. einen Eintrag, wird jeder Eintrag einzeln bearbeitet, d.h. die Nr wird gedruckt, rausgeschoben, abgeschnitten, wieder zurück gezogen und das nächste gedruckt.
Übergiebt man ein Band, wird das komplette Band fortflaufend gedruckt und geschnitten und erst bei einem neuen Band (oder Einzeleintrag) zurückgezogen.
Und manchmal bis zu 2000 Typenschilder gedruckt werden und die in aller Regel als Band vorliegen, ist die Einzelübergabe ungünstig.
Bisher habe ich das so realsiert, dass ich Delphi durch das SQL Ergebniss laufen und gruppieren lasse. Da jetzt ein neues Labelprogramm mit anderem Befehlsaufbau dazu kommt, wollte ich den Job komplett in MsSQL auslagen
Aber wenns nicht geht mach ich eben weiter wie bisher.

Danke trotzdem.

[jobo]

Mein Ergebnis

mit diesem Select

Zitat von jobo:

Code:

select GroupedSerNr, Typ, Status, Count(*) 
  from (
       SELECT SerNr, Typ, Status, 
              case 
                  when LAG(status,1,'R') OVER(ORDER BY SerNR) = status
                  then LAG(SerNR)        OVER(ORDER BY SerNR)
                  else serNr   
              end AS GroupedSerNr
         FROM daten
        WHERE SerNr BETWEEN 'F05323690' AND 'F05323695'
       ) x
group by GroupedSerNr, Typ, Status
order by GroupedSerNr

sieht so aus

Zitat von norwegen60:

und möchte sie so gruppieren, dass ich folgende Ausgabe bekommen

Code:

Min(SerNr)   Typ         Status   Anzahl
F05323690    MLC10U100   S        2
F05323692    MLC10U100   R        1 
F05323693    MLC10U100   S        2

http://sqlfiddle.com/#!3/da3ba/2

[norwegen60]

Hallo Jo,

ich denke die Ursache ist einfach zu erkären. Ich denke du gehst davon aus, dass es F05323692 nur mit Status R gibt. Real gibt es ihn mind. zwei mal. Den ursprünglichen Datensatz bei Auslieferung mit Status S und dann ein- oder mehrfach als Repaartur mit Status R. Beachtet werden soll aber nur der letzte, aktuellste Datensatz

Ändere ich das SQL auf

Code:

       SELECT SerNr, Typ, Status,
              case
                  when LAG(status,1,'R') OVER(ORDER BY SerNR) = status
                  then LAG(SerNR)       OVER(ORDER BY SerNR)
                  else serNr  
              end AS GroupedSerNr
         FROM (select * from Ser_nr where ID  in  (SELECT   Max(S1.ID) AS MaxID
                     FROM     Ser_Nr AS S1
                     WHERE s1.SerNr BETWEEN 'F05323690' AND 'F05323694'
                GROUP BY S1.SerNr)) y
       ) x
group by GroupedSerNr, Typ, Status
order by GroupedSerNr

funktioniert es mit dem SQL auch.

Code:

F05323690   MLC10U100   S   2
F05323692   MLC10U100   R   1
F05323693   MLC10U100   S   2

Erweitere ich aber den Ser-Nr. Bereich z.B. auf BETWEEN 'F05323690' AND 'F05323700' sieht das Ergebnis so aus

Code:

F05323690   MLC10U100   S   2
F05323692   MLC10U100   R   1
F05323693   MLC10U100   S   2
F05323694   MLC10U100   S   1
F05323695   MLC10U100   S   1
F05323696   MLC10U100   S   1
F05323697   MLC10U100   S   1
F05323698   MLC10U100   S   1
F05323699   MLC10U100   S   1
F05323700   MLC10U100   S   1

Korrekt wäre

Code:

F05323690   MLC10U100   S   2
F05323692   MLC10U100   R   1
F05323693   MLC10U100   S   8

[p80286]

Zitat von norwegen60:

Beachtet werden soll aber nur der letzte, aktuellste Datensatz

Wenn Du kein Datum in den Daten hast, gibt es zunächst keinen "aktuellsten" Datensatz.
U.U. ist es möglich über die ID eine zeitliche Abfolge zu rekonstruieren, aber solche Ergebnisse über Bande sind meiner Meinung nach nicht sehr zuverlässig.

Gruß
K-H