:::: MENU ::::

SCCM report for all expired adverts

SELECT v_Advertisement.AdvertisementID AS [Advertisement ID],
v_Advertisement.AdvertisementName AS [Advertisement Name],
v_Advertisement.PackageID AS [Package ID],
v_Package.Name AS [Package Name],
v_Advertisement.ProgramName AS [Program Name],
v_Collection.CollectionID AS [Collection ID],
v_Collection.Name AS [Collection Name],
v_Advertisement.PresentTime AS [Advertisement Creation Date],
v_Advertisement.ExpirationTime AS [Advertisement Expiration Date]
FROM v_Advertisement INNER JOIN
v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID INNER JOIN
v_Package ON v_Package.PackageID = v_Advertisement.PackageID
WHERE (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETDATE()) AND (v_Advertisement.PresentTimeIsGMT = 0) OR (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETUTCDATE()) AND (v_Advertisement.PresentTimeIsGMT = 1)

One Comment

So, what do you think ?