Schleife bei SQL SELECT

[ibuddy1]

Hallo,

ich habe hier ein Script, was soweit funktioniert, aber sicherlich einfacher zu gestalten ist, per Schleife. Kann mir das einer sagen?

Danke

SELECT        au.Name, a.Bezeichnung, ad.SerialNo, ad.InventNo, ad.PrinterIP,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()))) AS Zähler0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'black') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()))
                               ORDER BY TimeUTC DESC) AS schwarz0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'cyan') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()))
                               ORDER BY TimeUTC DESC) AS cyan0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'magenta') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()))
                               ORDER BY TimeUTC DESC) AS magenta0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'yellow') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()))
                               ORDER BY TimeUTC DESC) AS gelb0,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 1))) AS Zähler1,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'black') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 1))) AS schwarz1,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'cyan') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 1))) AS cyan1,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'magenta') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 1))) AS magenta1,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'yellow') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 1))) AS gelb1,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 2))) AS Zähler2,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'black') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 2))) AS schwarz2,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'cyan') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 2))) AS cyan2,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'magenta') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 2))) AS magenta2,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'yellow') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 2))) AS gelb2,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() - 3))) AS Zähler3
                          

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 COLLATE Latin1_General_CI_AS = ad.InventNo INNER JOIN
                         scOffice61.dbo.Artikel AS a ON a.ArtikelNummer COLLATE Latin1_General_CI_AS = ma.ArtikelNummer COLLATE Latin1_General_CI_AS
WHERE        (ad.Obstacles = '0')      

Das geht bis hin zu 30 Werten.

Wie kann man das mit Schleifen regeln?

Danke

[chorn]

probier doch mal

AND (SnmpColorant IN ('black', 'cyan'))

[Rolf b]

Hallo chorn,

das wird nicht helfen, gebraucht wird ja der jeweils neueste Eintrag des Tages zur jeweiligen Farbe.

Ich habe gestern eine Weile über das Thema nachgedacht, und ich bin sicher, dass man das besser lösen kann, aber möglicherweise nicht in einer einzigen Query.

Eventuell wird es mit Views oder Common Table Expressions besser, einen konkreten Vorschlag habe ich (noch) nicht. Das war mir bisher zu zeitaufwändig.

Rolf

[apfelsine]

Hi,

Schleifen kann man mit einem Cursor anhandeln. So ungefähr... :

DECLARE C1 CURSOR for 


SELECT SnmpColorant , sValue From dbo.ACCSNMPHISTORY
WHERE (Name LIKE '%Toner%' OR Name LIKE '%Kassette%') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE()
	
	OPEN C1
	FETCH NEXT FROM C1 INTO @SnmpColorant , @value
	WHILE @@FETCH_STATUS = 0
	BEGIN
@spalten = @spalten + @value + ' as ' + @SnmpColorant + ','

END

lg apfelsine

[ibuddy1]

ich bin ein stück weiter gekommen. HAbe eine Schleife erstellt. Aber jetzt macht er pro Tag eine eigene Abfrage, ich würde das aber in einer Tabelle haben und nicht in 30.…

declare @tag INT = 0
declare @max INT = 30
WHILE(@tag <= @max)
BEGIN
SELECT        au.Name, a.Bezeichnung, ad.SerialNo, ad.InventNo, ad.PrinterIP,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))) AS Zähler0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'black') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS schwarz0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'cyan') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS cyan0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'magenta') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS magenta0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'yellow') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS gelb0

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 COLLATE Latin1_General_CI_AS = ad.InventNo INNER JOIN
                         scOffice61.dbo.Artikel AS a ON a.ArtikelNummer COLLATE Latin1_General_CI_AS = ma.ArtikelNummer COLLATE Latin1_General_CI_AS
WHERE        (ad.Obstacles = '0')

SET @tag = @tag +1
END

[apfelsine]

Hi,

Dann sammel alles in einem String und mach ein gesammeltes Execute auf den String.

lg apfelsine

[ibuddy1]

wie geht das denn?

[ibuddy1]

wer kann hier helfen?

[Rolf b]

Hallo ibuddy1,

Apfelsines Idee KÖNNTE sein, dass Du das SQL nicht mühsam hinschreibst, sondern mit einem Programm die 5x30=150 Spaltenabfragen generierst und das so erhaltene SQL Monster dem Server vor die Füße kübelst.

Das Problem ist, dass Du alle Informationen zu einem Gerät in EINER Zeile stehen haben willst, inclusive Historie.

SQL ist nicht dafür gemacht worden, Abfrageergebnisse in zwei Richtungen dynamisch zu erzeugen, d.h. eine variable Anzahl von Spalten kannst Du mit Schleifen oder UNION nicht erreichen. Du MUSST jede Spalte, die herauskommen soll, einzeln im SELECT aufführen.

Aber selbst wenn SQL das dynamisch könnte - eine Query mit 150 Spalten ist auf keinen Fall sinnvoll - wer soll sich das anschauen. Sinnvoll wäre eine Zeile pro Device und Tag, das sind dann 10 Spalten.

WAS willst Du mit den Daten tun? Wäre eine Zeile pro Device und Tag eine brauchbare Alternative?

Rolf

[ibuddy1]

ich muss aus den 30 Werten (je Farbe) bestimmte Berechnungen machen. Eine wäre bspw. der Mittelwert pro Farbe.

Wenn das natürlich direkt ginge, wäre ich noch glücklicher.

[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

[apfelsine]

Hi,

ich war im Urlaub.…

Also du möchtest alle Farben nebeneinander in einer Zeile haben. Wenn ich so ein Problem habe, dann stopfe ich meine erforderlichen Spalten in einer Schleife in meinen String und führe am Ende meinen String aus. Ich gehe davon aus, das du irgendwo deine Auflistung deiner Farben hast. Wenn nicht musst du dir dazu eben was anderes einfallen lassen:

	WHILE @@FETCH_STATUS = 0
	BEGIN
@tausendspalten = @tausendspalten + @farbe+ ' as ' + @SnmpColorant + ','

  END

@meinSelect = 'SELECT ' + @tausendspalten + ' FROM BLA'

sp_executesql(@meinSelect)

ich hoffe das klärt es auf

lg apfelsine