SQL Count and avg help

[rsunra]

G'day

I have Follow SQL statment

SELECT convert(char(08),[call start],3) as Day,

SUM(CASE WHEN DATEPART(hour,[call start]) = 0 THEN 1 ELSE 0 END) AS '00',

SUM(CASE WHEN DATEPART(hour,[call start]) = 1 THEN 1 ELSE 0 END) AS '01',

SUM(CASE WHEN DATEPART(hour,[call start]) = 2 THEN 1 ELSE 0 END) AS '03',

SUM(CASE WHEN DATEPART(hour,[call start]) = 3 THEN 1 ELSE 0 END) AS '04',

.
.
.

SUM(CASE WHEN DATEPART(hour,[call start]) = 23 THEN 1 ELSE 0 END) AS '23'

What this does is counts the number of calls in the hour

What I need to do is get in a nice table or tables the has
the following inforamtion

Avg # of by hour for each day for the past 5 weeks and past week..

The Table could like some thing like


Sun Mon Thu . . . Sat
00 5 7 8 2
01 12 52 43 34
.
. (YOU GET THE IDEA)
.
22 12 54 21 12
23 4 5 78 134



Any Idea??

Cheers

Rich

 

[donutman]

Rich,
If I understand your question correctly, then all you need to do is tweak your query a little and turn it into a view so that you can run a group by query on the view.
The initial query (make it a view) should be:

SELECT DatePart(Weekday, [call start]) as DayOfWeek,
DatePart(hour,[call start]) as HourStart FROM tTableOfCallStarts WHERE
[call start]>SomeDate-36 and [call date]<SomeDate

That should give you a 5 week range of the basic data that you need. Now write another query against that view as:

SELECT HourStart, sum(case DayOfWeek when 0 then .2 else 0), sum(case DayOfWeek when 1 then .2 else 0), ... sum(case DayOfWeek when 6 then .2 else 0) FROM AboveView GROUP BY HourStart

The reason for the .2 instead of using 1 is that you said you wanted a 5 week average so every call is "worth" .2 (1 divided by 5). Doing this avoids writing another query against this query to do the division.

Your result table is now in the format requested. BTW, if your DB engine allows you to write cross tab queries than the 2nd query above could be simplified.
-Karl