ibuddy1: Schleife bei SQL SELECT

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

  1. probier doch mal

    AND (SnmpColorant IN ('black', 'cyan'))
    
    1. 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

      --
      Dosen sind silbern
  2. 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

    1. 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
      
      
      1. Hi,

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

        lg apfelsine

        1. wie geht das denn?

          1. wer kann hier helfen?

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

              --
              Dosen sind silbern
              1. 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.

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

                  --
                  Dosen sind silbern
          2. 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