mar311 (3/14/2011)

---

Thanks for the feedback. What I'm ultimately looking for is to create new columns for the "Grand Totals" and then pull the single value from that column via SP to reflect on the front end of the web app.

See my changes in bold below to see what I'm trying to achieve. If someone can give me an example query of how to do this, I would be extremely appreciative.


select @locname=facilityname from gwgeneral..locations where locid=@locid
select location=isnull(@locname,'All Locations')
select
location,
name,
yst=ystitemcount,
MTD=mtditemcount,
YTD=ytditemcount,
ystphr=cast(case when ystminutes > 0 then round(ystitemcount/(ystminutes/60.0),0) else 0 end as int),
MTDphr=cast(case when MTDminutes > 0 then round(MTDitemcount/(MTDminutes/60.0),0) else 0 end as int),
YTDphr=cast(case when YTDminutes > 0 then round(YTDitemcount/(YTDminutes/60.0),0) else 0 end as int),
ystGRANDTOTAL= sum(yst),
MTDGRANDTOTAL= sum(MTD),
YTDGRANDTOTAL= sum(YTD)

WITH CTE AS (
select
location,
name,
yst=ystitemcount,
MTD=mtditemcount,
YTD=ytditemcount,
ystphr=cast(case when ystminutes > 0 then round(ystitemcount/(ystminutes/60.0),0) else 0 end as int),
MTDphr=cast(case when MTDminutes > 0 then round(MTDitemcount/(MTDminutes/60.0),0) else 0 end as int),
YTDphr=cast(case when YTDminutes > 0 then round(YTDitemcount/(YTDminutes/60.0),0) else 0 end as int)
)
SELECT *,
       ystGRANDTOTAL= sum(yst) OVER (PARTITION BY location),
       MTDGRANDTOTAL= sum(MTD) OVER (PARTITION BY location),
       YTDGRANDTOTAL= sum(YTD) OVER (PARTITION BY location)
  FROM CTE;

mar311 (3/14/2011)

---

Unfortunately Wayne that did not work. If you look at the query in my original post, how could we structure the syntax in a way to get your suggestion working properly? Or anyone else that wants to chime in? I haven't figured out a way to do this successfully yet.

Thanks for your input!