:::: MENU ::::

Failed Advert Machine list Based on Site Code Wise

SELECT DISTINCT

v_R_System.AD_Site_Name0 AS [AD Site Code], v_Advertisement.ProgramName,

v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Last Status Message], v_ClientAdvertisementStatus.LastStateName AS [Last Status],

v_ClientAdvertisementStatus.LastExecutionResult, COUNT(v_ClientAdvertisementStatus.LastStatusMessageIDName) AS ‘Error  Count’

FROM         v_ClientAdvertisementStatus INNER JOIN

v_R_System ON v_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN

v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN

v_Package ON v_Package.PackageID = v_Advertisement.PackageID

WHERE     (v_ClientAdvertisementStatus.AdvertisementID IN (‘AdvertID1’,

‘AdvertID2’)) AND (v_ClientAdvertisementStatus.LastStateName = ‘Failed’)

GROUP BY v_ClientAdvertisementStatus.LastExecutionResult, v_Advertisement.ProgramName, v_ClientAdvertisementStatus.LastStatusMessageIDName,

v_ClientAdvertisementStatus.LastStateName, v_ClientAdvertisementStatus.LastExecutionResult, v_R_System.AD_Site_Name0

ORDER BY v_Advertisement.ProgramName


2 Comments

So, what do you think ?