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