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

[stefanieburton]

 Hi Jitendra, 

Thanks for responding.  I am reading through the net trying to understand how to use pivot and unpivot.  But if you have experience with how to write the query,  I will take any help I can get.

Thanks

Stefanie

 

 

On 09/13/13, Jitendra Ramesh Kalyankar<jite...@microsoft.com> wrote:

 

Hi Stefanie –

 

Try using UNPIVOT command in the last query. You can get few examples around UNPIVOT command if you Bing it. Let me know if you need help in writing it.

 

Thanks,

JitenRK

 

From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com]On Behalf Of stefaniebur...@verizon.net
Sent: Friday, September 13, 2013 12:53 AM
To: mssms@lists.myitforum.com
Subject: [mssms] SQL SUM Rows - Status of a Specific Advertisment

 

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 @Totalint

declare @Acceptedint

declare @Inprogressint

select @Total=count(*),

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

@InProgress=count(caseWHEN LastStateIN(7,8,10,12)THEN 0end)

--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 LastAcceptanceStateNameas C013,count(*)as C015,

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

AdvertisementID

from v_ClientAdvertisementStatus

where AdvertisementID=' '--@AdvertID

groupby LastAcceptanceStateName, AdvertisementID

 

select LastStateNameas C017,count(*)as C015,ROUND(100.0*count(*)/@Accepted,1)as C016,

AdvertisementID

from v_ClientAdvertisementStatus

where AdvertisementID=' '--@AdvertID

and LastState!=0groupby LastStateName, AdvertisementID

 

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

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

AdvertisementID

from v_ClientAdvertisementStatus

whereAdvertisementID=''--@AdvertID

and LastState!=0

groupby LastStateName, AdvertisementID