[mssms] SQL SUM Rows - Status of a Specific Advertisment

[stefanieburton]

I am trying to create a new report very similar to the Status of the Specific Advertisment Report. I have to provide management an excel report that adds the total of waiting, running, retrying and reboot pending rows into a row called inProgress.  I would be great if I could create a SCCM Report that does this for me, that way I do not have to recreate the excel report after each deployment.  I have figured out how to get the total for those fields ( last select statement) but I am having trouble trying to figure out how to add that entry as a row and not a column.

 

Heres what I have so far.  Any help would be greatly appreciated.

 

 

 

declare @Total int

declare @Accepted int

declare @Inprogress int

select @Total=count(*),

@Accepted=sum(case LastState WHEN 0 THEN 0 ELSE 1 end),

@InProgress=count(case WHEN LastState IN (7,8,10,12) THEN 0 end)

--when 7 then 2 LastStateName Waiting

--when 8 then 2 LastStateName Running

--when 10 then 2 LastStateName Retrying

--when 12 then 2 LastStateName Reboot Pending

--end)

from v_ClientAdvertisementStatus

where AdvertisementID=' '--@AdvertID

 

select LastAcceptanceStateName as C013, count(*) as C015,

ROUND(100.0*count(*)/@Total,1) as C016,

AdvertisementID

from v_ClientAdvertisementStatus

where AdvertisementID=' '--@AdvertID

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=' '--@AdvertID

and LastState!=0 group by LastStateName, AdvertisementID

 

select LastStateName as C017, count(*) as C015, @InProgress as InProgress,

ROUND(100.0*count(*)/@Accepted,1) as C016,

AdvertisementID

from v_ClientAdvertisementStatus

where AdvertisementID=''--@AdvertID

and LastState!=0

group by LastStateName, AdvertisementID