Daten per SQL gruppieren

[norwegen60]

Zitat von haentschman:

...bei Labeldruckern würde ich mich auf SDK des Druckers verlassen.

Das mache ich auch. Ich erzeuge nur eine Command-Datei (EasyLabel) bzw. Integration-File (Bartender). Bei beiden könnte ich eine Liste aller Typenschilder übergeben oder eben die Anzahl der Typenschilder und die Start-Serien-Nr. Bei einer Liste wird jedes Typenschild wie ein Einzeldruck behandelt, d.h. (Typenschild drucken - Vorschub - Schneiden - Wieder einziehen, nächstes Typenschild). Gebe ich die Anzahl an, ist Drucken und Schneiden ein fließender Vorgang, d.h. es wird nicht jedes mal zurück gezogen.

Übergabe sähe schematisch einmal so aus

Code:

Anzahl = 2
AbSerNr, Typ, Status
123456, Musteryp, S

Anzahl = 1
AbSerNr, Typ, Status
123458, Musteryp, R

Anzahl = 2
AbSerNr, Typ, Status
123459, Musteryp, S

und einmal so

Code:

AbSerNr, Typ
123456, Musteryp, S
123457, Musteryp, S
123458, Musteryp, R
123459, Musteryp, S
123460, Musteryp, S

Wie schon gesagt, bei dem Beispiel wäre die zweite Variante durchaus vertretbar. Druckt man aber viele Typenschilder, nervt das hin und hergeziehe und führt auch gerne zu Falten am Thermotransferband

Mit dem SQL von Jobo sieht das Ergebnis so aus

Code:

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

entspricht also auch nicht dem was ich wollte. Ich schau mir aber mal die Funktion LAG an. Die kenn ich nämlich noch nicht

[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

[jobo]

Zitat von norwegen60:

ich denke die Ursache ist einfach zu erkären.

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

Also die Sache mit den eindeutigen R hatte ich irgendwo in den Beiträgen gesehen, dachte aber das wäre Standardprogramm bzw nicht das Problem.

Dass die Erweiterung auf größere Folgen nicht klappt war mir nicht aufgefallen. Blöd.
Dazu fällt mir spontan auch keine Lösung ein, weil es nach meinem Kenntnisstand keine Konstruktion in SQL Server gibt, die eine autarke Wertefolge unabhängig von irgendwelchen Randbedingungen produziert. Also auf Deutsch eine normale Sequenz gibt es leider nicht. Damit wär es m.E. gelöst.

Wahrscheinlich kann man auch mit rekursiven Queries was machen oder irgendwelchen Workarounds dafür, aber dazu hatte ich noch keine Lust.

[Jasocul]

Himitsu hat doch eigentlich den richtigen Ansatz schon gesagt.
Eine Function sollte das Problem relativ einfach beheben.

Ich habe mal was auf die Schnelle gebastelt:

Code:

CREATE FUNCTION dbo.f_SerNr (
)
RETURNS @Etiketten TABLE
(
SerNr varchar(10),
Typ varchar(10),
Status char(1),
Anzahl int
)

AS
BEGIN
  declare
    @SerNr varchar(10),
    @SerNr_1 varchar(10),
    @Typ varchar(10),
    @Status char(1),
    @Typ_1 varchar(10),
    @Status_1 char(1),
    @Anzahl int

  declare SN_Cursor Cursor for
  select *
    from Ser_Nr
   order by SerNr;

  open SN_Cursor

  fetch next from SN_Cursor
  into @SerNr, @Typ, @Status

  set @Anzahl = 1

  while @@FETCH_STATUS = 0
  begin
    fetch next from SN_Cursor
    into @SerNr_1, @Typ_1, @Status_1
    
    if (@Typ = @Typ_1) and (@Status = @Status_1) and (@@FETCH_STATUS = 0) 
    BEGIN
      set @Anzahl = @Anzahl + 1
    end
    else
    begin
      insert @Etiketten values (@SerNr, @Typ, @Status, @Anzahl)
      set @SerNr = @SerNr_1
      set @Typ = @Typ_1
      set @Status = @Status_1
      set @Anzahl = 1
    end
  end

  close SN_Cursor;
  DeAllocate SN_Cursor;
  return
end

Ich habe das jetzt ohne Parameter gemacht. Daher fehlt im Cursor noch die Einschränkung auf den gewünschten Bereich.
Ich denke, dass die paar Ergänzungen aber kein Problem darstellen sollten.

[jobo]

Zitat von Jasocul:

Himitsu hat doch eigentlich den richtigen Ansatz schon gesagt.
Eine Function sollte das Problem relativ einfach beheben.

Ja, das ist richtig. "Relativ einfach" ist halt relativ.
Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.
Da ja auch ebenso auch eine clientseitige Lösung im Raum steht, mit der man es analog zu einer SP machen kann, habe ich das nicht betrachtet. Ein elegantes Select reizt mich da mehr. Dass einem dabei offenbar die Einschänkungen der SQL Server Sequenzimplemntierung in die Hacken laufen, habe ich nicht geahnt.

[Jasocul]

Zitat von jobo:

Ja, das ist richtig. "Relativ einfach" ist halt relativ.
Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.

Es ist keine SP, sondern eine UDF. Auf die kann man mit einem Select zugreifen.
Ein schönes SQL bevorzuge ich auch, aber es gibt Grenzen. Zumal im professionellem Umfeld auch Zeit eine große Rolle spielt.

Zitat von jobo:

Dass einem dabei offenbar die Einschänkungen der SQL Server Sequenzimplemntierung in die Hacken laufen, habe ich nicht geahnt.

Es gibt eine nicht dokumentierte Tabelle: master..spt_values
Die kann dir sowas liefern. Allerdings ist die auf 2048 Werte beschränkt. Der Thread-Ersteller schrieb, dass das bis zu 2000 Einträge werden können und ist damit dicht an der Grenze.
Ich habe die Tabelle auch schon genutzt mit einen fortlaufenden Datumswert zu generieren.
Beispiel:

Code:

SELECT DATEADD(Day, Number, '01.' + CAST(MONTH(@StartDatum) AS VARCHAR) + '.' + CAST(YEAR(@StartDatum) AS VARCHAR)) as Datum
          FROM master..spt_values 
         WHERE Type='P'
           AND DATEADD(day, Number, @StartDatum) <= @EndeDatum

Vielleicht kannst du damit ja noch was elegantes zaubern

[jobo]

Ok, SP oder UDF, unter dem Aspekt der Wartbarkeit, Komplexität, wie gesagt lieber SQL-natürlich performant, das wäre ja mit Window Functions erstmal zu erwarten.

Die SPT_VALUES ist ja offenbar auch fragwürdig. Zu "elegant" könnte man ja auch "dokumentiert" und "zuverlässig" zählen. Aber so eine alte Sybase System Tabelle, die knapp am Rangelimit ist, kann man nicht wirklich elegant nennen. Dann lieber Deine Lösung.

[p80286]

Ich würde eine Lösung über den Delphi Client bevorzugen, da SQL von Haus aus für Mengen und nicht für (sortierte) Folgen gemacht ist. Im Client kann ich die Abfolge der Daten problemlos berücksichtigen. das Problem jetzt mit SQL zu lösen mag zwar reizvoll sein, aber meiner Meinung nach ist das ineffizient.

Gruß
K-H