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:

Zitat von norwegen60 (Beitrag 1366845)

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:

Zitat von jfheins (Beitrag 1366850)

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. :stupid:

[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:

Zitat von jfheins (Beitrag 1366863)

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. :stupid:

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.

[himitsu]

Wie übergibst du das denn an den Drucker?

Nur weil du das im Programm "einzeln" verarbeitest, muß es auch nicht einzeln zum Drucker.
Du kannst das ja sammeln und dann gemeinsam zum Drucker schicken.

Falls die Schnittstelle das DataSet entgegen nimmt, dann entweder die Daten holen und in ein Memory-DataSet umkopieren
oder z.B. CachedUpdates=True und dann das DataSet bearbeiten (Edit/Delete) bevor es an den Drucker geht. (natürlich hinteher das CancelUpdates nicht vergessen)

[haentschman]

Moin...8-)

Zitat:

Wie übergibst du das denn an den Drucker?

...bei Labeldruckern würde ich mich auf SDK des Druckers verlassen. Da hast das Schneiden komplett im Griff... nach jedem 5. Etikett oder 10. Etikett oder nach Wechsel der Nummer oder beim Ende des Auftrages... Für das Etikett gibt es eine Vorlage...Daten rein...fertsch. :thumb:

[jobo]

Man muss ein Zwischenergebnis hinbekommen, dass es ermöglicht einer gewünschten Gruppe von Werten ein eindeutiges Gruppierungsmerkmal zuzuordnen.

Ist das gelungen, dann ist der Rest einfach Standard Group by.

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

Das Inner Select erzeugt die Unterscheidung der Gruppe über das "Detektieren" einer Wertänderung von Status und abhängig davon dann die Auswahl eines eindeutigen Gruppenmerkmals, hier die schon vorhandene oder vorige SerNr.

Offset und Defaultangabe von Lag(Status...) muss ggF. angepasst werden.

[norwegen60]

Zitat:

Zitat von haentschman (Beitrag 1366899)

...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:

Zitat von jobo (Beitrag 1366903)

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:

Zitat von norwegen60 (Beitrag 1366845)

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:

Zitat von norwegen60 (Beitrag 1366925)

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:

Zitat von norwegen60 (Beitrag 1366925)

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:

Zitat von Jasocul (Beitrag 1366950)

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:

Zitat von jobo (Beitrag 1366952)

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:

Zitat von jobo (Beitrag 1366952)

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 :wink:

[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

[jobo]

Zitat:

Zitat von p80286 (Beitrag 1366965)

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.

Im Prinzip sehe ich das auch so, ich würde aber eine SQL Lösung für solche Probleme nicht pauschal als ineffizient bezeichnen. "Window Functions" sind genau für das "Sortierproblem" gemacht, sie bieten dabei nicht nur eine simple Syntax, als die vielen Workarounds, die es so gibt, sie sind idR auch effizient(er als die Workarounds implementiert).

Wenn ich in Folge sowohl im SQL, einer SP/UDF oder im Client auf die Cursor / Loop Geschichte verzichten kann, ist es auch ein Pluspunkt (effizientere Entwicklung, weniger Resourcenverbrauch)
Wenn leider die Findung eines effizienten Statements an gewisser Funktionalität (oder Kenntnismangel) scheitert, ist man am Ende leider eben doch bei der Standardlösung.

Nach meinen lauen Erfahrungen ist MS mit den Window Functions leider etwas hinterher. Der 2008er Server hat etwas aufgeholt, der 2012 kann noch deutlich mehr, wird aber hier nicht eingesetzt.
Die Sequenz Thematik (die m.E. in diesem und anderen Bereichen eine weitere Steigerung böte/ Notwendigkeit ist), ist dann hier leider noch ein weiterer Sargnagel.

[himitsu]

Zitat:

Zitat von jobo (Beitrag 1366952)

Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.

Wenn nur als SELECT, dann eben das Problem auftrennen:

1)

SELECT auf alle Anfangs-Datensätze
* über einen JOIN auf IS NULL oder als Subselect mit NOT EXISTS
* alles auflisten, wo es keinen "zugehörigen" Datensatz mit AbSerNr-1 gibt

2)

und dann über ein rekursives SELECT alle nachfolgenden Datensätze suchen und dranjoinen
(frag mich bitte nicht wie, denn mit dem rekursiven steh ich auf Kriegsfuß und bei deinem DBMS ist das bestimmt eh anders, da ich es bissher nur im PostgreSql gemacht hatte)

oder jeweils den "nächsen" Anfangsdatensatz suchen (alle Anfangs-Datensätze die eine größere AbSerNr haben, das dann ORDER BY und LIMIT 1)
und über ein BETWEEN dann alle dazwischen liegenden AbSerNr dranjoinen

hier bekommen alle zusammengehörigen Datensasätze als GroupID z.B. die AbSerNr des jeweiligen Anfangs-Datensatzes

3)

Und zum Schluss dann GROUP BY und Count() drüber

[jobo]

Zitat:

Zitat von himitsu (Beitrag 1366976)

Zitat:

Zitat von jobo (Beitrag 1366952)

Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.

Wenn nur als SELECT, dann eben das Problem auftrennen:

Klar, dann ist man aber -aus meiner Sicht- eher auf der Ebene "Workaround", jenseits von schnell und effizient. Subselects in der Select Clause wären m.E. genau solche Kandidaten, die zwar alles möglich machen, aber zu fürchterlichen Ausführungsplänen führen. Oft nicht schlimm für den Anfang (neue Implementierung, wenig Daten), dann aber mit mit wachsender Betriebsdauer und Datenvolumen kann man irgendwann zusehen, wie sortiert und aggregiert wird.
Der Vorschlag von jasucol mit einer alternativen Datenquelle für "generische" Sequenzen wäre da noch naheliegender, das ist in meinem Statement eingebaut übersichtlich und meinetwegen auch zukunftssicher, weil diese olle Systemtabelle so tief "mit drin" hängt, dass sie nie abgelöst wird. Aber dann ist da noch das 2041 Limit, was es hier konkret grenzwertig macht. (Dann noch eher eine eigene Tabelle die Zahlen von 1 bis 10000 enthält - eben genug jedenfalls, und man wäre auf der sicheren Seite. Die kann man auch an anderer Stelle immer gebrauchen, wenn man keine vernünftigen Sequenzen hat, dann würde es noch mehr Sinn machen)

Recursive Aufrufe in 2008 weiß ich grad auch nicht auswendig, aber an der Stelle fliegt man bei MS SQL 2008 oder niedriger auch gern auf die Nase.
Da ist die gezeigte UDF dann schon ziemlich straight forward und sicher auch relativ flott. Oder man schreibt sie nochmal clientsetig um in Delphi, wie p80286 es machen würde.

Das sind alles so Themen, die im Grunde eigentlich interessant sind, wenn Leute hier aufschlagen und wissen wollen, welchen Server soll ich nehmen (siehe neulich). Bei MS stelle ich im DB Bereich leider oft fest, dass Standards oder Quasi Standards nicht vorhanden / umsetzbar / adaptierbar sind.

Bei den Sequenzen bspw fürchte ich fast, die Einschränkungen, die es dort gibt, sind nicht technischer Natur, sondern sollen den Anwender vor irgendwas schützen. Ich weiß nur nicht wovor.

[nahpets]

Irgendwie hat es mich gereizt.

Keine Ahnung ob brauchbar, getestet mit FireBird, dürfte aber auf andere Datenbanken übersetzbar sein.

SQL-Code:

select SerNr, Typ, Status, Anzahl from
(
  select SerNr, BisNr, Typ, Status,
    Cast(SubString(BisNr from 2 for 10) as Integer) - Cast(SubString(SerNr from 2 for 10) as Integer) As Anzahl
  from
  (
    select Min(SerNr) As SerNr, BisNr, Typ, Status from
    (
      select SerNr, Typ, Status,
        /* Unter Oracle wäre das nvl, bei MSSQL dürfte es IsNull sein. */
        /* Beim letzten Satz fehlt uns die BisNr für die Anzahlberechnung. */
        /* Daher wird hier die folgende "Krücke" benötigt. */
        /* Schön und / oder gar elegant ist was vollkommen anderes, */
        /* und durch das SubString-Gewusel wird das nícht unbedingt pflegeleicht */
        /* oder gar (welch Graus) auf andere SerNr-Arten anpassbar. */
        Coalesce((select min(b.SerNr) from norwegen60 b
                  where a.SerNr < b.SerNr
                  and  (   a.Status <> b.Status
                        or a.Typ <> b.Typ
                       )
                  ),
                  /* LPAD kennt MSSQL nicht. */
                  /* Da müsste man wohl mit sowas in der Art "rumkaspern".
                  /* right(replicate('0',8) + cast(cast(SubString(max(SerNr) from 2 for 10) as Integer) + 1 as varchar(15)),8) */
                  (select 'F'||lpad(cast(SubString(max(SerNr) from 2 for 10) as Integer) + 1,8,'0') from norwegen60)
                 ) as BisNr
      from norwegen60 a
      where SerNr Between 'F05323690' and 'F05323695'
    )
    group by BisNr, Typ, Status
  )
)
order by SerNr, Typ;

Ausgehend von meinen Testversuchen, sollte das Ganze nicht nur bei 'nem Statuswechsel funktionieren, sondern auch bei gleichem Status aber wechselndem Typ.

Sollte die SerNr auch mit anderen Buchstaben beginnen oder gar am Anfang mehr oder weniger viele Buchstaben enthalten, dann wird das Ganze nicht mehr wirklich witzig.

Bei ca. 10000 Sätzen in der Ausgangstabelle und ca. 3200 Sätzen im Ergebnis, benötigt das SQL ca. 4 Sekunden.
Hardware: Ein sehr in die Jahre gekommener Laptop (D810 von Dell)

Viel Vergnügen beim Testen und Auseinanderpflücken ;-)

[norwegen60]

Hallo zusammen,

Danke für die vielen Inputs.
Wenn ich Zeit habe, werde ich mir den Ansatz von Naphet mal anschauen. Es hätte mich gereizt, aber da ich im Moment den neuen Treiber zum Laufen bringen muss werde ich die Gruppierung in Delphi belassen. Sie funktioniert ja bereits.
Es kommen nämlich noch genau die Probleme dazu die auch erwähnt wurden.

• Es ist nicht nur der Status der die Gruppierung bestimmt sondern noch ein paar andere Felder.
• Die DB beinhaltet ca 1.5 Mio Datensätze
• Wenns dumm läuft, ändert sich auch noch der Aufbau der Serien-Nr.

Danke trotzdem
Gerd

[nahpets]

Um das SQL auch auf andere Seriennummerntypen anwendbar zu machen, schlage ich vor, die Seriennummer in den konstanten Teil (in den Beispieldaten das F) und den nummerischen Teil, also die aufsteigende Nummer, in der Tabelle / Datenbank zu unterteilen. Das macht die weitere Verarbeitung deutlich einfacher, erfordert aber eine Datenmigration mit allen erforderlichen Nacharbeiten in vorhandenen Routinen.

Das von oben übernommene SQL könnte dann in etwa so aussehen (ungetestet):

SQL-Code:

select
  Concat(SerConst,Replicate('0', 8 - Len(SerNr)) + SerNr) As SerNr,
  Typ, Status,
  WeiteresFeld1, /* ... */ WeiteredFeldX,
  Anzahl from
(
  select
    SerConst,
    SerNr,
    BisNr,
    Typ,
    Status,
    WeiteresFeld1, /* ... */ WeiteresFeldX,
    (BisNr - SerNr) As Anzahl
  from
  (
    select
      SerConst,
      Min(SerNr) As SerNr,
      BisNr,
      Typ,
      Status,
      WeiteresFeld1, /* ... */ WeiteresFeldX
    from
    (
      select
        SerConst,
        SerNr,
        Typ,
        Status,
        WeiteresFeld1, /* ... */ WeiteresFeldX,
        IsNull((select
                  min(b.SerNr)
                from norwegen60 b
                where a.SerConst = b.SerConst
                and   a.SerNr < b.SerNr
                and ( a.Status <> b.Status
                      or a.Typ <> b.Typ
                      or a.WeiteresFeld1 <> or b.WeiteresFeld1
                      /* ... */
                      or a.WeiteresFeldX <> or b.WeiteresFeldX
                     )
                ),
                (select Concat(SerConst,Replicate('0', 8 - Len(Max(SerNr))) + Max(SerNr)) As SerNr
                 from norwegen60)
              ) as BisNr
      from norwegen60 a
      where SerConst = 'F' and SerNr Between 5323690 and 5323695
      /* Parametrisiert: */
      /* where SerConst = :ParamSerConst and SerNr Between :ParamStartSerNr and :ParamEndeSerNr */
    )
    group by SerConst, BisNr, Typ, Status, WeiteresFeld1, /* ... */ WeiteresFeldX
  )
)
order by 1, 2, 3, 4, 5 /* , ... NrLetztesFeldImSelect */;