July 18, 2006 at 10:44 pm
Either I have been looking at this too long, or it is just not making sense to me...
I have function that creates #tblTemp. From there, I have written 5 queries that all retrieve 3 records back (grouped by category called Class). The queries are as follows:
1 - Class, Sum(Volume) as 'Monthly Volume' ... group by class
2 - Class, Sum(Volume) as 'Business Volume' from #tblTemp Where Datepart(dw,vDate) NOT IN (1,7) group by class
3 - Class, Average(Volume) as 'Average' ... group by class
4 - Class, vDate, Volume from (Select Class, Max(Volume) from #tblTemp group by class) [Not exactly coded correctly]
--Retrieved the date, class, and total volume by class
What I would like to see is one query that would return the 3 records by list it in columns... for example
Class, Monthly Volume, Business Volume, Average, Max Volume and Date occurred
1
2
3
July 18, 2006 at 11:35 pm
SELECT Class,
Sum(Volume) as 'Monthly Volume',
Sum(case when Datepart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'Business Volume' ,
Average(Volume) as 'Average',
MAX( vDate) vDate,
Max(Volume) Volume
from #tblTemp
group by class
_____________
Code for TallyGenerator
July 19, 2006 at 9:08 am
Sleep does a wonder... one question/comment. I need to intergate the date of where the Max(Volume) occured. The stand-alone query that works right now is:
Select Class, vDate, Volume FROM #tblTemp Where Volume IN (Select MAX(Volume) From #tblTemp GROUP BY Class)
I really just need the date returned for this column in the aforementioned solution you provided.
July 20, 2006 at 10:23 am
Opening this up to the forurm... I need insert the following query as a column:
Select Class, vDate 'Date of Peak', Volume
FROM #tblTemp
Where Volume in (Select Max(Volume) From #tblTemp group by Class)
Into the following T-SQL statement:
Select Class, SUM(Volume) 'Total_ALL', MAX(Volume) as 'Peak',
SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',
AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'
FROM #tblTemp
GROUP BY Class
As noted, the T-SQL statement will return 3 records for the three classes. The problem is tying the date that the peak volume occured for the class, and tying it to the Class in the SELECT statement.
July 21, 2006 at 2:16 pm
Your challenge here is that you must aggregate the date since you are using a GROUP BY clause. Take a look at your first query,
Select Class, vDate 'Date of Peak', Volume
FROM #tblTemp
Where Volume in (Select Max(Volume) From #tblTemp group by Class)
In the event that your peak volume occurs more than once, this query will return more than one row. In order to fold this into your second SELECT, you would have to aggregate the vDate also and have your query return the 'Most Recent' date of peak volume for each Class.
Select Class,
SUM(Volume) 'Total_ALL',
MAX(Volume) as 'Peak',
MAX(vDate) as 'Date of Peak',
SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',
AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'
FROM #tblTemp
GROUP BY Class
July 28, 2006 at 1:59 pm
Thank you for your response... I am not sure how this would work, as it will just return the last date in the given month, not the date that contained the largest volume... sorry, any suggestions.
July 28, 2006 at 2:20 pm
This would be much easier if you could post the table DDL for your temp table and some sample data, but here goes a shot anyway....
Select Class,
SUM(Volume) 'Total_ALL',
MAX(Volume) as 'Peak',
t3.vDate as 'Date of Peak',
SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',
AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'
FROM #tblTemp t
INNER JOIN (Select t1.class, vDate
FROM #tblTemp t1
INNER JOIN (Select Class, Max(Volume) as Volume From #tblTemp group by Class) t2
ON t1.Volume = t2.Volume and t1.Class = t2.class) t3
ON t.class = t3.class
GROUP BY Class
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply