GROUPING Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUPING Question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-09-09 : 09:11:46

I have a table structure like the following

ID | Secs | DateInserted

SELECT count(ID) as AllCalls
,SUM(CASE WHEN Secs > 0 THEN 1 ELSE 0 END) as Answered
,SUM(CASE IsNull(Secs,0) WHEN 0 THEN 1 ELSE 0 END) as Unanswered
,DATEPART(hour, DateInserted) as theHour
FROM MyTable
WHERE convert(varchar,DateInserted,111) = convert(varchar,getdate(),111)
GROUP BY DATEPART(hour, DateInserted)
ORDER BY DATEPART(hour, DateInserted) ASC

And the above SQL gets me all the calls for the current day, grouped by hour.

What I need to is take this query and add to so that I can get all the same data but get the average for same day in the last 6 weeks.

So, if I were running the report today, I would want to get the above data, but the average of every Tuesday for the last six weeks

And I also want to get the average per week for the last six weeks.

Any help would be appreciated. Thanks alot

HC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 09:21:17

This can get you started

SELECT		COUNT(ID) AS AllCalls,
		SUM(CASE WHEN ISNULL(Secs, 0) > 0 THEN 1 ELSE 0 END) as Answered,
		SUM(CASE WHEN ISNULL(Secs, 0) = 0 THEN 1 ELSE 0 END) as Unanswered
FROM		MyTable
WHERE		DateInserted >= DATEDIFF(DAY, '19000212', GETDATE())
		AND DateInserted < DATEDIFF(DAY, '18991231', GETDATE())
		AND DATENAME(WEEKDAY, DateInserted) = 'Tuesday'

E 12°55'05.63"
N 56°04'39.26"

Harry C
Posting Yak Master

148 Posts

Posted - 2008-09-09 : 11:20:15

In the scenario above, how do I get the AVERAGE for each of the rows, still grouped by hour

SELECT COUNT(ID) AS AllCalls,
SUM(CASE WHEN ISNULL(Secs, 0) > 0 THEN 1 ELSE 0 END) as Answered,
SUM(CASE WHEN ISNULL(Secs, 0) = 0 THEN 1 ELSE 0 END) as Unanswered
FROM MyTable
WHERE DateInserted >= DATEDIFF(DAY, '19000212', GETDATE())
AND DateInserted < DATEDIFF(DAY, '18991231', GETDATE())
AND DATENAME(WEEKDAY, DateInserted) = 'Tuesday'

I need to get the avergage of AllCalls, Answered, Unanswered. Thanks....

Harry C
Posting Yak Master

148 Posts

Posted - 2008-09-09 : 12:28:51

Ok, this gets me part of the way. The only problem now is I dont know how to group the items the right way. See the bolded part below. How can I still get the display to be like

Any help is appreciated, I am under a tight deadline. Thanks!

AllCallsAvg AnsweredAvg UnansweredAvg theHour
14 1 13 0
14 2 12 1
7 0 7 2
4 0 4 3
1 0 1 4
2 0 2 5
4 0 4 6
7 0 7 7

SELECT CAST(AVG(theCount) as numeric(10,2)) as AvgCount FROM
(
SELECT CAST(Count(*) as numeric(10,2)) as theCount
FROM MyTable
WHERE convert(varchar,DateInserted,111) >= convert(varchar,dateadd(week, datediff(week, 0, getdate()) - 6, 0),111)
AND DATENAME(WEEKDAY, DateInserted) = DATENAME(WEEKDAY, getdate())
AND DATEPART(hour, DateInserted) = 0
GROUP BY DATEPART(week, DateInserted)
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources