SCCM NO Of Days of Advertisement status

SELECT DISTINCT v_Package.Name AS 'Package Name',(


v_ClientAdvertisementStatusSELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementID) AS 'Total Targeted Machines',(


v_ClientAdvertisementStatus


Status machines'SELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('No Status')) AS 'No ,


v_ClientAdvertisementStatus(SELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementID AND LastState!=0) AS 'Total Accepted Machines',


v_ClientAdvertisementStatus


PendINg'(SELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Succeeded', 'Reboot )) AS 'Total Succeeded Machines',(


v_ClientAdvertisementStatusSELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Failed')) AS 'Failed',(


v_ClientAdvertisementStatus


Status'SELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Accepted - No Further )) AS 'Accepted - No Further Status',(


v_ClientAdvertisementStatusSELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Retrying')) AS 'Retrying',(


v_ClientAdvertisementStatusSELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Running')) AS 'Running',(


v_ClientAdvertisementStatusSELECT COUNT(*) FROM v_ClientAdvertisementStatus WHERE .AdvertisementID=v_Advertisement.AdvertisementIDAND v_ClientAdvertisementStatus.LastStateName IN('Waiting')) AS 'Waiting',
v_Advertisement.AdvertisementName AS 'Advertisement Name',
v_Advertisement
.AdvertisementID AS 'Advertisement ID' FROM v_AdvertisementINNERJOIN v_Package ON v_Advertisement.PackageID = v_Package.PackageIDINNER
v_CollectionJOIN v_Collection ON v_Advertisement.CollectionID = .CollectionIDINNER
v_ClientAdvertisementStatus JOIN v_ClientAdvertisementStatus ON v_Advertisement.AdvertisementID = .AdvertisementIDAND
V_advertisement v_Advertisement.AdvertisementID in(SELECT advertisementid FROM WHERE DATEDIFF(D, V_advertisement.presenttime,GETDATE())<=60) ORDER BY v_Advertisement.AdvertisementName