March 13, 2011 at 10:16 pm
Am wanting to know the most efficient way to show SUM of a column derived from a subquery aggregate function. If you look at the query I provide below, how can I sum the columns yst, MTD, YTD, ystphr, MTDphr, YTDphr and then reflect the total sum of each column?
Would really appreciate any example queries anyone can provide. Thanks for your time!
declare @effdate datetime, @locid int, @showAll bit, @fystart datetime, @locName varchar(50)
set @effdate = getdate()
set @locid = 20
set @showAll = 1
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)
from (
select
location=l.fac,
name=isnull(rtrim(e.fname)+' '+left(e.lname,1),'Unknown'),
s.locid,
staffid=cast(p.staffid as int),
p.stationid,
ystitemcount=sum(case dt when dateadd(d,-1,@effdate) then itemcount else 0 end),
mtditemcount=sum(case when month(dt)=month(@effdate) and year(dt)=year(@effdate) then itemcount else 0 end),
ytditemcount=sum(case when dt between @fystart and @effdate then itemcount else 0 end),
ystminutes=sum(case dt when dateadd(d,-1,@effdate) then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end),
mtdminutes=sum(case when month(dt)=month(@effdate) and year(dt)=year(@effdate) then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end),
ytdminutes=sum(case when dt between @fystart and @effdate then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end)
from ptotals p
join stations s on s.stationid=p.stationid
join gen..locations l on l.locid=s.locid
left join gen..vwAllees e on ltrim(e.empno)=p.staffid
where (@locid = 0 or s.locid=@locid)
group by l.fac,s.locid,e.fname,e.lname,cast(p.staffid as int),p.stationid
) x
where ystitemcount > 0 or ytditemcount > 0
and (@showAll = 1 or ystitemcount > 0)
order by location,name
March 13, 2011 at 11:04 pm
If I understand you correctly, you need an aggregation, and then an aggregation of all aggregates (or more commonly, a summary row). You may want to try exploring the possibility of using the GROUPING() Aggregate function - http://msdn.microsoft.com/en-us/library/ms178544.aspx
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 14, 2011 at 12:00 am
I believe you're looking for the ROLLUP operator.
http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 14, 2011 at 8:43 am
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)
March 14, 2011 at 8:54 am
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)
Does this work?
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;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 14, 2011 at 2:25 pm
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!
March 14, 2011 at 2:59 pm
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!
Can you provide some sample data, ddl, and expected result from that sample data? It would help tremendously towards us understanding your final expected goal. You can check the first link in my sig if you need assistance with that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 16, 2011 at 10:32 am
Absolulety..and I should have done this to begin with. Apologies.
Here's the result set I'm looking to achieve.
ystMTDYTD ystphrMTDphrYTDphr*ystGRANDTOTAL**MTDGRANDTOTAL**YTDGRANDTOTAL*
--------- ------------------ --------------- --------------- ---------------
7012006200 200 180 100 590 5800 58200
520460052000 120 100 90
March 16, 2011 at 10:47 am
I attached an img for ease of reading.
March 21, 2011 at 7:18 am
Happy Monday to everyone! Was hoping there was someone out there that can review this thread and provide a solution. Any help would be greatly appreciated!
March 22, 2011 at 9:44 am
the image does indeed make it easy to read, but... there are many VERY good SQL volunteers on this site that always seem eager to help but you have to help them. please see this link
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for guidelines of how to get the best help. In summary, we want to see working create table statements along with inserts to populate test data.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply