For a specific Advertisement status for last 3 Days…

---for a specific Advertisement status for last 3 Days… if we include the Set   @AdvName = '%' line then and remove the above line in red color will show for all advertisements with last 3 days status

-- To include all we need to give % in SQL this is a tip

Declare     @AdvName Varchar(256)
Set   @AdvName = 'Lync_2010_Full_Install'

---Set   @AdvName = '%'  
Select      adv.AdvertisementName
,    adv.AdvertisementID
,    s.Host
,    LastAcceptanceMessageIDName
,    LastAcceptanceStateName
,    LastAcceptanceStatusTime
,    LastStatusMessageIDName
,    LastStateName
,    LastStatusTime
,    LastExecutionResult
From  (
      Select      AdvertisementName
      ,    AdvertisementID
      From  dbo.v_AdvertisementInfo
      Where AdvertisementName Like @AdvName
      ) As  adv
Join  (
      Select      AdvertisementID
      ,    ResourceID
      ,    LastAcceptanceMessageIDName
      ,    LastAcceptanceStateName
      ,    LastAcceptanceStatusTime
      ,    LastStatusMessageIDName
      ,    LastStateName
      ,    LastStatusTime
      ,    LastExecutionResult
      From  dbo.v_ClientAdvertisementStatus
      Where LastStatusTime >= DateAdd(d,-3,GetDate())
      ) As  cas
On    adv.AdvertisementID = cas.AdvertisementID
Join  (
      Select      ResourceID
      ,    Netbios_Name0 As Host
      From  dbo.v_R_System
      Where Client0 = 1
      And  Active0 = 1
      And  Obsolete0 = 0   
      ) As  s
On    cas.ResourceID = s.ResourceID
Order By
      AdvertisementName
,    Host