24 hour Advertisement report
I had to create a report today that only shows the newest numbers for an advertisement. I wanted to use it for the Health Checks. This report will show me the results of an advertisement for the last 24 hours. I'm using it in a dashboard to show me how well my health check solutions are working on a daily basis.
Just change the advertisement number to one that works for you.
Hope this helps someone else out.
--******************************************************
declare @Total int
declare @Accepted int
select @Total=count(*)
, @Accepted=sum(case LastState when 0 then 0 else 1 end)
from v_ClientAdvertisementStatus
where AdvertisementID= 'XXX20019'
SELECT LastAcceptanceStateName AS C013
,count (*) as C015
,ROUND(100.0*count(*)/@Total,1) as C016
,AdvertisementID
FROM v_ClientAdvertisementStatus
WHERE ((AdvertisementID = 'XXX20019')and(LastStatusTime > GETDATE() - 1))or((AdvertisementID = 'XXX20017')and(LastAcceptanceStateName = 'No Status'))
GROUP BY LastAcceptanceStateName, AdvertisementID
SELECT LastStateName AS C017
,count(*) as C015
,ROUND(100.0*count(*)/@Accepted,1) as C016
,AdvertisementID
FROM v_ClientAdvertisementStatus
WHERE (AdvertisementID = 'XXX20019') AND (LastState <> 0)and (LastStatusTime > GETDATE() - 1)
GROUP BY LastStateName, AdvertisementID