Jitendra
Any chance you would still be willing to assist? I was not able to get PIVOT/UNpIVOT to work. I also tried CASE and GROUP BY - Having but no luck. Thanks Stefanie From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Jitendra Kalyankar Sent: Friday, September 13, 2013 11:54 AM To: stefaniebur...@verizon.net; mssms@lists.myitforum.com Subject: RE: RE: [mssms] SQL SUM Rows - Status of a Specific Advertisment Sure will write something that would help but not before Monday. Hth Jitendra Sent from my Windows Phone _____ From: stefaniebur...@verizon.net Sent: 13-09-2013 20:28 To: mssms@lists.myitforum.com Subject: Re: RE: [mssms] SQL SUM Rows - Status of a Specific Advertisment 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 <mailto:AdvertisementID=''--@AdvertID> =''--@AdvertID and LastState!=0 groupby LastStateName, AdvertisementID
