Busiest Hour - 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 2000 Forums

Transact-SQL (2000)

Busiest Hour

Author

Topic

brand0nmcd
Starting Member

7 Posts

Posted - 2010-09-28 : 09:06:40

Hi all,

First post here so be nice.

I have been searching high and low for a query that will give me the businest hour for a client for a time frame.

The day is stored by half hour and I have been successful in retreiving the busiest half hour by client but because I need to group by hour any time I try to join on the combined field I get an error.

Let me post some sample data and what the expected results should be and see what we can come up with.

If this was already discussed here please post a link. THANKS!!

brand0nmcd
Starting Member

7 Posts

Posted - 2010-09-28 : 09:18:04

DateTime CallTypeID EnterpriseName CallsOfferedHalf
2010-09-16 09:30:00 5513 Houston_ISD_Eng_Agent_Queue 6
2010-09-16 16:30:00 5513 Houston_ISD_Eng_Agent_Queue 5
2010-09-16 17:30:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 10:00:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 10:30:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 08:30:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 12:00:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 14:00:00 5513 Houston_ISD_Eng_Agent_Queue 4
2010-09-16 14:30:00 5513 Houston_ISD_Eng_Agent_Queue 3
2010-09-16 11:30:00 5513 Houston_ISD_Eng_Agent_Queue 3
2010-09-16 08:00:00 5513 Houston_ISD_Eng_Agent_Queue 3
2010-09-16 09:00:00 5513 Houston_ISD_Eng_Agent_Queue 2
2010-09-16 16:00:00 5513 Houston_ISD_Eng_Agent_Queue 2
2010-09-16 18:00:00 5513 Houston_ISD_Eng_Agent_Queue 1
2010-09-16 07:00:00 5513 Houston_ISD_Eng_Agent_Queue 1
2010-09-16 12:30:00 5513 Houston_ISD_Eng_Agent_Queue 1
2010-09-16 13:00:00 5513 Houston_ISD_Eng_Agent_Queue 1
2010-09-16 07:30:00 5513 Houston_ISD_Eng_Agent_Queue 0
2010-09-16 11:00:00 5513 Houston_ISD_Eng_Agent_Queue 0
2010-09-16 17:00:00 5513 Houston_ISD_Eng_Agent_Queue 0
2010-09-16 13:00:00 5990 National_Grid_Agent_Queue 10
2010-09-16 12:30:00 5990 National_Grid_Agent_Queue 10
2010-09-16 08:30:00 5990 National_Grid_Agent_Queue 9
2010-09-16 09:00:00 5990 National_Grid_Agent_Queue 6
2010-09-16 11:00:00 5990 National_Grid_Agent_Queue 6
2010-09-16 10:00:00 5990 National_Grid_Agent_Queue 6
2010-09-16 09:30:00 5990 National_Grid_Agent_Queue 5
2010-09-16 14:30:00 5990 National_Grid_Agent_Queue 5
2010-09-16 13:30:00 5990 National_Grid_Agent_Queue 5
2010-09-16 15:00:00 5990 National_Grid_Agent_Queue 4
2010-09-16 11:30:00 5990 National_Grid_Agent_Queue 4
2010-09-16 07:30:00 5990 National_Grid_Agent_Queue 4
2010-09-16 07:00:00 5990 National_Grid_Agent_Queue 4
2010-09-16 08:00:00 5990 National_Grid_Agent_Queue 3
2010-09-16 10:30:00 5990 National_Grid_Agent_Queue 3
2010-09-16 12:00:00 5990 National_Grid_Agent_Queue 3
2010-09-16 14:00:00 5990 National_Grid_Agent_Queue 0
2010-09-16 15:30:00 5990 National_Grid_Agent_Queue 0
2010-09-16 10:00:00 7109 DC_Mercer_NOR_Agent_Queue 42
2010-09-16 11:00:00 7109 DC_Mercer_NOR_Agent_Queue 34
2010-09-16 10:30:00 7109 DC_Mercer_NOR_Agent_Queue 32
2010-09-16 09:00:00 7109 DC_Mercer_NOR_Agent_Queue 32
2010-09-16 09:30:00 7109 DC_Mercer_NOR_Agent_Queue 28
2010-09-16 11:30:00 7109 DC_Mercer_NOR_Agent_Queue 28
2010-09-16 08:30:00 7109 DC_Mercer_NOR_Agent_Queue 27
2010-09-16 14:30:00 7109 DC_Mercer_NOR_Agent_Queue 25
2010-09-16 13:30:00 7109 DC_Mercer_NOR_Agent_Queue 24
2010-09-16 08:00:00 7109 DC_Mercer_NOR_Agent_Queue 22
2010-09-16 15:00:00 7109 DC_Mercer_NOR_Agent_Queue 13
2010-09-16 13:00:00 7109 DC_Mercer_NOR_Agent_Queue 11
2010-09-16 15:30:00 7109 DC_Mercer_NOR_Agent_Queue 10
2010-09-16 12:00:00 7109 DC_Mercer_NOR_Agent_Queue 9
2010-09-16 07:30:00 7109 DC_Mercer_NOR_Agent_Queue 9
2010-09-16 18:00:00 7109 DC_Mercer_NOR_Agent_Queue 8
2010-09-16 12:30:00 7109 DC_Mercer_NOR_Agent_Queue 7
2010-09-16 14:00:00 7109 DC_Mercer_NOR_Agent_Queue 7
2010-09-16 07:00:00 7109 DC_Mercer_NOR_Agent_Queue 6
2010-09-16 16:30:00 7109 DC_Mercer_NOR_Agent_Queue 5
2010-09-16 16:00:00 7109 DC_Mercer_NOR_Agent_Queue 4
2010-09-16 17:30:00 7109 DC_Mercer_NOR_Agent_Queue 3
2010-09-16 19:00:00 7109 DC_Mercer_NOR_Agent_Queue 3
2010-09-16 18:30:00 7109 DC_Mercer_NOR_Agent_Queue 2
2010-09-16 20:00:00 7109 DC_Mercer_NOR_Agent_Queue 1
2010-09-16 17:00:00 7109 DC_Mercer_NOR_Agent_Queue 1

brand0nmcd
Starting Member

7 Posts

Posted - 2010-09-28 : 09:24:38

Expected result would be:

CallTypeID, Busiest Hour, Volume
5513, 9:00, 8 (two hours with same volume select earliest)
5590, 13:00,15
7109, 10:00,74

Any assistance would be greatly appreciated.

- Brandon

brand0nmcd
Starting Member

7 Posts

Posted - 2010-09-30 : 11:24:01

Any body?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 11:34:51

It would be easier if you had provided a data-script to populate the table

I'm thinking that you could use a ROW_NUMBER() OVER (PARTITION BY [CallTypeID], DATEPART(HOUR, [DateTime]) ORDER BY [CallTypeID]) AS [hourCount]

in a derived table and then look up the highest value of [hourCount] in the outer query -- I'm too busy to make up a sample table with your data in it to test however,

Good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 11:37:23

Sorry -- just noticed that you are posting in a 2000 forum ROW_NUMBER isn't available.

please disregard. Sorry.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 11:57:17

Hi. Here's what I've got so far. I don't have a 2000 instance to test on.


DECLARE @data TABLE (
	[callDateStamp] DATETIME
	, [callTypeID] INT
	, [EnterpriseName] VARCHAR(255)
	, [CallsOfferedHalf] INT
	)
INSERT @data (
	[callDateStamp]
	, [callTypeID]
	, [EnterpriseName]
	, [CallsOfferedHalf]
	)
      SELECT '2010-09-16T09:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	6
UNION SELECT '2010-09-16T16:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	5
UNION SELECT '2010-09-16T16:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	5
UNION SELECT '2010-09-16T16:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	5
UNION SELECT '2010-09-16T17:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T10:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T10:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T08:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T12:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T14:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	4
UNION SELECT '2010-09-16T14:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	3
UNION SELECT '2010-09-16T11:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	3
UNION SELECT '2010-09-16T08:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	3
UNION SELECT '2010-09-16T09:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	2
UNION SELECT '2010-09-16T16:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	2
UNION SELECT '2010-09-16T18:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	1
UNION SELECT '2010-09-16T07:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	1
UNION SELECT '2010-09-16T12:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	1
UNION SELECT '2010-09-16T13:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	1
UNION SELECT '2010-09-16T07:30:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	0
UNION SELECT '2010-09-16T11:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	0
UNION SELECT '2010-09-16T17:00:00',	5513,	'Houston_ISD_Eng_Agent_Queue',	0
UNION SELECT '2010-09-16T13:00:00',	5990,	'National_Grid_Agent_Queue',	10
UNION SELECT '2010-09-16T12:30:00',	5990,	'National_Grid_Agent_Queue',	10
UNION SELECT '2010-09-16T08:30:00',	5990,	'National_Grid_Agent_Queue',	9
UNION SELECT '2010-09-16T09:00:00',	5990,	'National_Grid_Agent_Queue',	6
UNION SELECT '2010-09-16T11:00:00',	5990,	'National_Grid_Agent_Queue',	6
UNION SELECT '2010-09-16T10:00:00',	5990,	'National_Grid_Agent_Queue',	6
UNION SELECT '2010-09-16T09:30:00',	5990,	'National_Grid_Agent_Queue',	5
UNION SELECT '2010-09-16T14:30:00',	5990,	'National_Grid_Agent_Queue',	5
UNION SELECT '2010-09-16T13:30:00',	5990,	'National_Grid_Agent_Queue',	5
UNION SELECT '2010-09-16T15:00:00',	5990,	'National_Grid_Agent_Queue',	4
UNION SELECT '2010-09-16T11:30:00',	5990,	'National_Grid_Agent_Queue',	4
UNION SELECT '2010-09-16T07:30:00',	5990,	'National_Grid_Agent_Queue',	4
UNION SELECT '2010-09-16T07:00:00',	5990,	'National_Grid_Agent_Queue',	4
UNION SELECT '2010-09-16T08:00:00',	5990,	'National_Grid_Agent_Queue',	3
UNION SELECT '2010-09-16T10:30:00',	5990,	'National_Grid_Agent_Queue',	3
UNION SELECT '2010-09-16T12:00:00',	5990,	'National_Grid_Agent_Queue',	3
UNION SELECT '2010-09-16T14:00:00',	5990,	'National_Grid_Agent_Queue',	0
UNION SELECT '2010-09-16T15:30:00',	5990,	'National_Grid_Agent_Queue',	0
UNION SELECT '2010-09-16T10:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	42
UNION SELECT '2010-09-16T11:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	34
UNION SELECT '2010-09-16T10:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	32
UNION SELECT '2010-09-16T09:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	32
UNION SELECT '2010-09-16T09:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	28
UNION SELECT '2010-09-16T11:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	28
UNION SELECT '2010-09-16T08:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	27
UNION SELECT '2010-09-16T14:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	25
UNION SELECT '2010-09-16T13:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	24
UNION SELECT '2010-09-16T08:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	22
UNION SELECT '2010-09-16T15:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	13
UNION SELECT '2010-09-16T13:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	11
UNION SELECT '2010-09-16T15:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	10
UNION SELECT '2010-09-16T12:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	9
UNION SELECT '2010-09-16T07:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	9
UNION SELECT '2010-09-16T18:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	8
UNION SELECT '2010-09-16T12:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	7
UNION SELECT '2010-09-16T14:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	7
UNION SELECT '2010-09-16T07:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	6
UNION SELECT '2010-09-16T16:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	5
UNION SELECT '2010-09-16T16:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	4
UNION SELECT '2010-09-16T17:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	3
UNION SELECT '2010-09-16T19:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	3
UNION SELECT '2010-09-16T18:30:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	2
UNION SELECT '2010-09-16T20:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	1
UNION SELECT '2010-09-16T17:00:00',	7109,	'DC_Mercer_NOR_Agent_Queue',	1

SELECT
	[callTypeID]
	, [CallHour]
	, SUM([callsOfferedHalf]) AS [callsPerHour]
FROM
	(
	SELECT
		[callDateStamp]
		, [callTypeID]
		, [EnterpriseName]
		, [CallsOfferedHalf]
		, DATEPART(HOUR, [callDateStamp]) AS [CallHour]
	FROM
		@data
	)
	AS [dets]
GROUP BY
	[callTypeID]
	, [Callhour]
ORDER BY
	[callTypeID]
	, [callHour]

Currently gives you each callId and the hour and the number of calls.

If this was 2005 or better this question would be really easy.

Got to go. hope someone else can help you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 12:09:23

Here's a 2005 version of what Charlie described:

;WITH a(CallTypeID, HOUR, Calls) AS 
	(SELECT CallTypeID, DATEPART(HOUR,[datetime]), SUM(CallsOfferedHalf) 
	FROM @data
	GROUP BY CallTypeID, DATEPART(HOUR,[datetime]) ),
	b(CallTypeID, HOUR, Volume, rn) AS 
	(SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY CallTypeID ORDER BY Calls DESC, HOUR) 
	FROM a)
SELECT CallTypeID, HOUR, Volume FROM b WHERE rn=1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-30 : 12:19:03

Using Transact Charlie's data.

SELECT
	[callTypeID],
	dateadd(hh,datediff(hh,0,[callDateStamp]),0) as [CallHour],
	SUM([callsOfferedHalf]) AS [callsPerHour]
FROM
	@data

GROUP BY
	[callTypeID],
	dateadd(hh,datediff(hh,0,[callDateStamp]),0)
ORDER BY
	[callTypeID],
	dateadd(hh,datediff(hh,0,[callDateStamp]),0)

Results:

callTypeID  CallHour                                               callsPerHour 
----------- ------------------------------------------------------ ------------ 
       5513 2010-09-16 07:00:00.000                                           1 
       5513 2010-09-16 08:00:00.000                                           7 
       5513 2010-09-16 09:00:00.000                                           8 
       5513 2010-09-16 10:00:00.000                                           8 
       5513 2010-09-16 11:00:00.000                                           3 
       5513 2010-09-16 12:00:00.000                                           5 
       5513 2010-09-16 13:00:00.000                                           1 
       5513 2010-09-16 14:00:00.000                                           7 
       5513 2010-09-16 16:00:00.000                                           7 
       5513 2010-09-16 17:00:00.000                                           4 
       5513 2010-09-16 18:00:00.000                                           1 
       5990 2010-09-16 07:00:00.000                                           8 
       5990 2010-09-16 08:00:00.000                                          12 
       5990 2010-09-16 09:00:00.000                                          11 
       5990 2010-09-16 10:00:00.000                                           9 
       5990 2010-09-16 11:00:00.000                                          10 
       5990 2010-09-16 12:00:00.000                                          13 
       5990 2010-09-16 13:00:00.000                                          15 
       5990 2010-09-16 14:00:00.000                                           5 
       5990 2010-09-16 15:00:00.000                                           4 
       7109 2010-09-16 07:00:00.000                                          15 
       7109 2010-09-16 08:00:00.000                                          49 
       7109 2010-09-16 09:00:00.000                                          60 
       7109 2010-09-16 10:00:00.000                                          74 
       7109 2010-09-16 11:00:00.000                                          62 
       7109 2010-09-16 12:00:00.000                                          16 
       7109 2010-09-16 13:00:00.000                                          35 
       7109 2010-09-16 14:00:00.000                                          32 
       7109 2010-09-16 15:00:00.000                                          23 
       7109 2010-09-16 16:00:00.000                                           9 
       7109 2010-09-16 17:00:00.000                                           4 
       7109 2010-09-16 18:00:00.000                                          10 
       7109 2010-09-16 19:00:00.000                                           3 
       7109 2010-09-16 20:00:00.000                                           1 

(34 row(s) affected)

CODO ERGO SUM

brand0nmcd
Starting Member

7 Posts

Posted - 2010-09-30 : 13:00:39

Here my attempt but since you cannot join on an aggregate it wont work...

SELECT
CT.EnterpriseName as [Call Type],
convert(varchar, HH.DateTime,101) as [Date],
Datepart(hour,HH.DateTime) as [Interval],
sum(HH.CallsOfferedHalf) as [Call Volume]

FROM
mhrs_awdb.dbo.Call_Type_Half_Hour HH
inner join mhrs_awdb.dbo.Call_Type CT on HH.CallTypeID=CT.CallTypeID
inner join (select
CallTypeID,
convert(varchar, DateTime,101) as [Date],
Datepart(hour,DateTime) as [Hour],
sum(CallsOfferedHalf) as [SumOff]
from
mhrs_awdb.dbo.Call_Type_Half_Hour
group by CallTypeID,Datepart(hour,DateTime),convert(varchar, DateTime,101)) S
on S.CallTypeID=CT.CallTypeID and S.SumOff=sum(HH.CallsOfferedHalf) and S.Date=convert(varchar, HH.DateTime,101) and S.Hour=Datepart(hour,HH.DateTime)

WHERE
HH.DateTime between '09/16/2010' and '09/17/2010'
group by CT.EnterpriseName, convert(varchar, HH.DateTime,101),Datepart(hour,HH.DateTime)
order by 1, 2

brand0nmcd
Starting Member

7 Posts

Posted - 2010-10-06 : 09:46:22

I'd really hate to think that the only way is a stored procedure...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-06 : 10:01:30

quote:
I'd really hate to think that the only way is a stored procedure...

Why? Stored procedures are the best feature of SQL Server.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-06 : 10:40:58

Using Transact Charlie's data:

The best way is to use a temp table as follows:


SELECT callTypeID
	,DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101') AS callDateStamp
	,SUM(CallsOfferedHalf) AS CallsOffered
INTO #temp
FROM @data
GROUP BY callTypeID, DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101')

SELECT T1.callTypeID
	,MIN(T1.callDateStamp) AS BusiestHour
	,T1.CallsOffered AS Volume
FROM #temp T1
	JOIN
	(
		SELECT callTypeID, MAX(CallsOffered) AS CallsOffered
		FROM #temp T2
		GROUP BY callTypeID
	) D
		ON T1.callTypeID = D.callTypeID
			AND T1.CallsOffered = D.CallsOffered
GROUP BY T1.callTypeID, T1.CallsOffered

If you want to do it all in one statement, then you will have to duplicate the temp table logic:


SELECT T1.callTypeID
	,MIN(T1.callDateStamp) AS BusiestHour
	,T1.CallsOffered AS Volume
FROM
(
	SELECT callTypeID
		,DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101') AS callDateStamp
		,SUM(CallsOfferedHalf) AS CallsOffered
	FROM @data
	GROUP BY callTypeID, DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101')
) T1
	JOIN
	(
		SELECT callTypeID, MAX(CallsOffered) AS CallsOffered
		FROM
		(
			SELECT callTypeID
				,DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101') AS callDateStamp
				,SUM(CallsOfferedHalf) AS CallsOffered
			FROM @data
			GROUP BY callTypeID, DATEADD(hour, DATEDIFF(hour, '20000101', callDateStamp), '20000101')		
		) T2
		GROUP BY callTypeID
	) D
		ON T1.callTypeID = D.callTypeID
			AND T1.CallsOffered = D.CallsOffered
GROUP BY T1.callTypeID, T1.CallsOffered

yonabout
Posting Yak Master

112 Posts

Posted - 2010-10-07 : 06:01:41

You can do it without temp tables - just an alias like this:

select
calltypeid,
min(TimePeriod) TimePeriod,
max(calls) calls

from

	(
	SELECT
	calltypeid,
	dateadd(mi,(datediff(mi,0,CallDateStamp)/60*60),0) TimePeriod, 
	sum(CallsOfferedHalf) Calls
	FROM 
	@data
	GROUP BY 
	calltypeid,
	dateadd(mi,(datediff(mi,0,CallDateStamp)/60*60),0)
	) CallsPerhour

group by 
calltypeid

Cheers,

Yonabout

brand0nmcd
Starting Member

7 Posts

Posted - 2010-10-14 : 10:16:16

Yonabout aka Youtheman,

This worked great!

FINAL SQL for BUSIEST HOUR BY CALLTYPE ID:
SELECT
CallTypeID,
min(TimePeriod) as [TimePeriod],
max(Calls) as [Calls]

FROM
(
SELECT
CallTypeID,
dateadd(mi,(datediff(mi,0,DateTime)/60*60),0) as [TimePeriod],
sum(CallsOfferedHalf) as [Calls]
FROM
mhrs_awdb.dbo.Call_Type_Half_Hour
WHERE
DateTime between '10/08/2010' and '10/09/2010'
GROUP BY
CallTypeID,
dateadd(mi,(datediff(mi,0,DateTime)/60*60),0)
) CallsPerhour

Where
1=1

GROUP BY
CallTypeID
ORDER BY 1

1=1 (only needed if you enter SQL into CUIS)

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-14 : 10:58:38

What does "Busiest Hour" mean?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources