Query String für Differenzsumme

Question

Nochmal Hallo Ihr Wissenden,

meine erste Frage ist so perfekt beantwortet worden, da stell ich gleich mein nächstes und hoffentlich
erstmal letztes Problem ein.

Habe eine Tabelle in der in der Regel jede Minute ein Datensatz gespeichert wird.
Spalten sind ID, Zeit (datetime), Zähler (integer), Auftragsnummer (char)

Der Inhalt der Spalte Zähler ist in der Regel jedes mal höher, als der vorangegange Datensatz. Gleich geht auch, jedoch niemals kleiner oder NULL.

Der Inhalt der Spalte Auftragsnummer ändert sich natürlich wenn ein neuer Auftrag angelegt wird. Das ist nicht
jede Min der Fall. Also kann über Wochen hinweg die gleiche Auftragsnummer vorhanden sein.

Nun möchte ich die Gesamtmenge innerhalb eines Auftrages ermitteln. Im Prinzip bräuchte ich doch nur
den Wert der Spalte Zähler des ersten Datensatzes der enstprechenden Auftragsnummer vom letzten Wert der Spalte
Zähler des letzten Datensatzes für diese Auftragsnummer abziehen...

Geht das überhaupt mit einer Query Abfrage ?

Grüße ToBo

Answer 1

Das "Problem" bei so etwas ist, dass man "ersten" und "letzten" Datensatz unterschiedlich definieren kann. Schau Dir mal dies http://msdn.microsoft.com/en-us/library/ms189461.aspx (Beispiel B) an. Vielleicht hilft Dir das schon weiter. Ansonsten solltest Du mal ein paar Beispieldaten posten.

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 2

Nun, das verstehe ich nicht... der erste Datensatz ist der älteste und hat demzufolge die kleinste ID für diesen
Auftrag. Der letzte ist der jüngste und hat natürlich die größe ID für diesen Auftrag...

Wenn ich die Datesätze mit order by ID sortiere ist im Prinzip der erste und letzte doch klar ?

Select * from Tabelle Where Auftragsnummer = 'FA36000'
order by ID

Answer 3

Hallo Tobo,

das Problem sind solche Aussagen wie "Der Inhalt der Spalte Zähler ist in der Regel jedes mal höher,"
denn leider wird man schnell feststellen, das die Regel häufiger verletzt wird als einem lieb ist.

Probiere mal ob die folgenden, relativ einfachen Anweisungen Dir bereits das gewünschte Ergebnis
liefern, denn (bis auf die erste) kümmern sie sich nicht so sehr um größer oder kleiner:

USE tempdb
GO

CREATE TABLE dbo.Tabelle
(
	id int identity(1, 1) NOT NULL PRIMARY KEY,
	Zeit datetime NOT NULL,
	Zaehler int NOT NULL,
	Auftragsnummer char(10) NOT NULL)
GO	
	
INSERT INTO dbo.Tabelle VALUES('20090620 15:00.00', 1000, 'FA36000')
INSERT INTO dbo.Tabelle VALUES('20090620 15:01.00', 1001, 'FA36000')
INSERT INTO dbo.Tabelle VALUES('20090621 10:00.00', 1002, 'FA36000')
INSERT INTO dbo.Tabelle VALUES('20090621 10:00.00', 1002, 'FA36000')
INSERT INTO dbo.Tabelle VALUES('20090625 10:00.00', 1005, 'ZZ99999')
GO

-- kann zu Fehlern führen wenn MAX/MIN Wert ausser der Reihe
SELECT Auftragsnummer,
	MAX(Zaehler) - MIN(Zaehler) AS Differenz
FROM dbo.Tabelle
-- WHERE Auftragsnummer = 'FA36000' 
GROUP BY Auftragsnummer
GO	

-- Zaehlt die Anzahl von Einträgen für eine Auftragsnummer
SELECT Auftragsnummer,
	COUNT(*) AS Anzahl
FROM dbo.Tabelle
-- WHERE Auftragsnummer = 'FA36000' 
GROUP BY Auftragsnummer
GO

-- Zaehlt hier die Anzahl unterschiedlichen Einträgen
SELECT Auftragsnummer,
	COUNT(DISTINCT Zaehler) AS Anzahl
FROM dbo.Tabelle
-- WHERE Auftragsnummer = 'FA36000' 
GROUP BY Auftragsnummer
GO

Paßt das Ergebnis in keinem Falle, wären Beispieldaten wie gezeigt hilfreich.

Gruß Elmar

Answer 4

Oh... Phantastisch und funktioniert prima....

Mein Dank ist Dir gewiss zu 1000 times...

Gruß.

ToBo

Answer 5

Tja, es musste ja so kommen. Leider habe ich etwas nicht bedacht, welches mir heute
aufgefallen ist. Die obige Lösung ist zwar genial einfach (wenn mann's weiß), aber
liefert leider nicht das gewünschte Ergebnis.

Folgendes habe ich vergessen zu bedenken. Es kommt vor das Aufträge nicht beendet
und andere Aufträge zwischengeschoben werden. Der Zähler wird nicht bei jedem
Auftrag genullt, sondern ist ein Lebenszähler, der immer weiter hochgezählt wird.

Mit der obigen Lösung werden die MIN und Max Werte des Zähler zwar nur für
diesen einen Auftrag addiert. Kommt aber nun ein anderer Auftrag dazwischen,
beinhaltet der Max Wert, wenn dann der alte Auftrag fortgeführt wird natürlich
den Zwischenauftrag ebenfalls.

Eigentlich müsste mann Blockweise Min und Max Differenz berechnen, für Datensätze
mit zusammenhängenden ID's

Ich meine
Datensatz von ID 100 bis 500 Auftrag 1 = Differenz Auftrag 1 A

Datensatz von ID 501 bis 600 Auftrag 2 = Differenz Auftrag 2 A

Datensatz von ID 601 bis 1000 Auftrag 1 = Differenz Auftrag 1 B

Gesamtsumme = Differenz Auftrag 1 A + Differenz Auftrag 1 B

Natürlich weiss man die ID Bereiche nicht.

Geht das als Query Abfrage ?

Grüße.

ToBo

Answer 6

Hallo ToBo,
es wäre schön gewesen, wenn Du das Beispiel-Skript von Elmar um ein paar Zeilen ergänzt hättest und das gewünschte Ergebnis dazu gepostet hättest. Ich will mal versuchen, ob ich es richtig verstanden habe. Evtl. gibt es noch eine elegantere Lösung über die OLAP-Funktionen, aber so könnte es auch gehen.

1.) Weiterer Beispielsatz

INSERT INTO dbo.Tabelle VALUES('20090621 10:00.00', 1006, 'FA36000')

2.) Abfragen

-- Bilde die möglichen Kombinationen
SELECT t1.Auftragsnummer, t1.Zaehler, t2.Zaehler as Z2, t2.Zaehler - t1.Zaehler as Differenz
FROM dbo.Tabelle t1
left join dbo.Tabelle t2
on t1.Auftragsnummer = t2.Auftragsnummer
WHERE t1.Zaehler <= t2.Zaehler
and not exists(Select * from dbo.Tabelle t3 where t3.Zaehler between t1.Zaehler and t2.Zaehler and t3.Auftragsnummer < > t1.Auftragsnummer)
and not exists(Select * from dbo.Tabelle t4 where t4.Zaehler < t1.Zaehler and t4.Auftragsnummer = t1.Auftragsnummer
	and not exists(Select * from dbo.Tabelle t5 where t5.Zaehler between t4.Zaehler and t1.Zaehler and t5.Auftragsnummer < > t1.Auftragsnummer))
;

-- Summiere die möglichen Kombinationen
Select Auftragsnummer, max(Differenz) as Differenz
from (
SELECT t1.Auftragsnummer, t1.Zaehler, t2.Zaehler as Z2, t2.Zaehler - t1.Zaehler as Differenz
FROM dbo.Tabelle t1
left join dbo.Tabelle t2
on t1.Auftragsnummer = t2.Auftragsnummer
WHERE t1.Zaehler <= t2.Zaehler
and not exists(Select * from dbo.Tabelle t3 where t3.Zaehler between t1.Zaehler and t2.Zaehler and t3.Auftragsnummer < > t1.Auftragsnummer)
and not exists(Select * from dbo.Tabelle t4 where t4.Zaehler < t1.Zaehler and t4.Auftragsnummer = t1.Auftragsnummer
	and not exists(Select * from dbo.Tabelle t5 where t5.Zaehler between t4.Zaehler and t1.Zaehler and t5.Auftragsnummer < > t1.Auftragsnummer))
) x
-- WHERE Auftragsnummer = 'FA36000' 
group by x.Auftragsnummer, Zaehler
;

---

Einen schönen Tag noch, Christoph Muthmann Microsoft SQL Server MVP, http://www.insidesql.org

Answer 7

Hallo ToBo,

Haben Dir die Antworten geholfen?

Grüße,
Robert

Answer 8

Hallo ToBo,

Ich gehe davon aus, dass die Antwort Dir weitergeholfen hat.

Grüße,
Robert