SELECTCOUNT(*)ASCount, v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName

FROM v_ClientAdvertisementStatus LEFTOUTERJOIN

v_Advertisement ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID

WHERE (v_ClientAdvertisementStatus.LastStateName =‘succeeded’)AND(v_ClientAdvertisementStatus.LastStatusTime >DATEADD(day, 30,GETDATE()))

GROUPBY v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName

ORDERBYCountDESC