Schleife bei SQL SELECT

[Rolf b]

Hallo ibuddy1,

wenn's nur die klassischen Aggregatfunktionen von SQL sind (Minimum, Maximum, Durchschnitt, Varianz, Anzahl), kann man mit GROUP Konstrukten etwas erreichen, und dafür ist dann sogar hilfreich, wenn man die Tage in unterschiedlichen Zeilen hat.

Frage wäre dann, ob es Tage gibt, für die Du in der ACCSNMPHISTORY Tabelle keine Werte hast und wie Du mit solchen Fehltagen umzugehen hast.

Prinzipiell kann man auf folgender Query aufbauen, um den "frischesten" History-Wert pro Farbe und Tag in einer Query zu erhalten. Je nach Auswertung, die Du machen willst, kannst Du diese Query auf jeden Fall als Grundlage verwenden.

SET @minDate = DATEADD(day, -30, CONVERT(date, GETDATE()))

SELECT DeviceId, SmnpColorant, sValue, maxTime
FROM ACCSMNPHISTORY h1 
   JOIN (SELECT DeviceId, SmnpColorant, MAX(TimeUTC) as maxTime
         FROM ACCSMNPHISTORY h2 
         WHERE TimeUTC >= @minDate
           AND (Name LIKE '%Toner%' OR Name LIKE '%Kassette%')
         GROUP BY DeviceId, SmnpColorant, CONVERT(date, TimeUTC)) hmax
   ON h1.DeviceId = hmax.DeviceId 
      AND h1.SmnpColorant = hmax.SmnpColorant
      AND h1.TimeUTC = hmax.maxTime

Diese Query liefert Dir pro Farbe den letzten Wert des jeweiligen Tages über die letzten 30 Tage. Sie funktioniert so, dass die Table mit einer GROUP BY Auswertung ihrer selbst gejoint wird. Diese GROUP BY Auswertung liefert pro DeviceId und Farbe den größten Timestamp je Tag. Der Join sucht dazu dann den passenden Farbwert.

Wenn Du für Auswertungen mit SQL Aggregatfunktionen auskommst, kannst Du diese Query wieder in eine FROM Klausel packen, nach Device und Farbe gruppieren dann verdichten.

SELECT x.DeviceId, x.SmnpColorant
     , MIN(x.sValue) as minValue, MAX(x.sValue) as maxValue, AVG(x.sValue) as avgValue
FROM (
      SELECT DeviceId, SmnpColorant, sValue, maxTime
      FROM ACCSMNPHISTORY h1 
         JOIN (SELECT DeviceId, SmnpColorant, MAX(TimeUTC) as maxTime
               FROM ACCSMNPHISTORY h2 
               WHERE TimeUTC >= @minDate
                 AND (Name LIKE '%Toner%' OR Name LIKE '%Kassette%')
               GROUP BY DeviceId, SmnpColorant, CONVERT(date, TimeUTC)) hmax
         ON h1.DeviceId = hmax.DeviceId 
            AND h1.SmnpColorant = hmax.SmnpColorant
            AND h1.TimeUTC = hmax.maxTime
     ) x
GROUP BY x.DeviceID, x.SmnpColorant

Das ist eine Query, die Dir pro Device und Farbe die MIN, MAX und AVG Werte der letzten 30 Tage liefert.

Etwas ähnliches kannst Du mit den Countern machen. Das schaffst Du selbst.

Je nach Anzahl von Devices kannst Du Dir nun diese Query-Ergebnisse in den Speicher saugen, die Devices abfragen und für die Ausgabe die Statistikergebnisse dazumischen. Eine Mega-Query der folgenden Art KANN man auch machen, aber das solltest Du genauer auf Performance messen, weil sie die Color Query viermal ausführt. Ob der SQL Server das vernünftig optimieren kann, weiß ich nicht.

SELECT au.Name, a.Bezeichnung, ad.SerialNo, ad.InventNo, ad.PrinterIP
     , counter.minValue, counter.maxValue, counter.avgValue
     , black.minValue, black.maxValue, black.avgValue
     , cyan.minValue, cyan.maxValue, cyan.avgValue
     , magenta.minValue, magenta.maxValue, magenta.avgValue
     , yellow.minValue, yellow.maxValue, yellow.avgValue
FROM  dbo.ACCDEVICES AS ad 
      INNER JOIN dbo.ACCUSERS AS au ON au.Id = ad.SubmitterId
      INNER JOIN scOffice61.dbo.Maschinen AS ma ON ma.MaschinenNummer = ad.InventNo
      INNER JOIN scOffice61.dbo.Artikel AS a ON a.ArtikelNummer = ma.ArtikelNummer 
      LEFT JOIN (...CounterQuery ) as counter ON counter.DeviceId=ad.Id
      LEFT JOIN (...ColorQuery ) as black ON black.DeviceId=ad.Id and cyan.snmpColorant='black'
      LEFT JOIN (...ColorQuery ) as cyan ON black.DeviceId=ad.Id and cyan.snmpColorant='cyan'
      LEFT JOIN (...ColorQuery ) as magenta ON black.DeviceId=ad.Id and cyan.snmpColorant='magenta'
      LEFT JOIN (...ColorQuery ) as yellow ON black.DeviceId=ad.Id and cyan.snmpColorant='yellow'
WHERE (ad.Obstacles = '0')

Ich übernehme keine Garantie für Richtigkeit - ich habe deine DB nicht und kann das nicht testen. Wenn Du Auswertungen brauchst, die über die Aggregatfunktionen von SQL Server hinausgehen, dann funktioniert das nicht und du musst basierend auf der Color-Query selbst auswerten.

Die Pivot-Funktionen des SQL Servers habe ich mir auch angeschaut, aber die helfen dir nicht wenn Du mehr als einen Aggregatwert pro Farbe brauchst.

Rolf