Historical Average of Hourly Transaction Count - Part 2 1

[larrydavid]

Thanks to George who helped me with getting the hourly average of the count of total transactions:

--AVG of TxCount
SELECT AVG(TxCount * 1.0) as [AVG_CLAIM/HR] FROM
(
SELECT DATEADD(hour, DATEDIFF(hour, getdate(), t.Timestamp), getdate()) AS TxHour,
COUNT(*) AS TxCount
FROM TEST t
GROUP BY DATEDIFF(hour, getdate(), t.Timestamp)
)x

Ultimately I need to display this in an Excel chart, with the axis set-up as hours and this moving average displaying as connectors on the chart. This is why I am tryng to capture the average for previous hours throughout the day starting at 6:00 am. I realize the query above gets me the hourly average, but is there a way to capture this average as it changes throughout the day? Shouldn't it be possible to simply get previous record counts that were entered for previous hours and get those average counts? This is a toughie I can't seem to get my head around. Thanks for any help.

Thanks,
Larry

 

[markros]

For a way to solve such queries check similar thread

http://social.msdn.microsoft.com/Fo...l/thread/35703b2d-efbf-430e-94d8-27c43b72fd09

PluralSight Learning Library

 

[larrydavid]

Hi Mark,

Here is what finally worked for me. I tried ranking functions, etc., but this was my final solution:

select	 IDENTITY(int,1,1) as Row, DATEDIFF(hour, getdate(), Timestamp) as HoursDiff, 
			 COUNT(*) as TxCount 
	into	 #tmpHours
	from	 Test group by DATEDIFF(hour, getdate(), Timestamp)
	order by DATEDIFF(hour, getdate(), Timestamp) asc

	select	 DATEADD
	(
			 hour, H1.HoursDiff, getdate()) as TxHour, 
			 (
				select AVG(TxCount * 1.0)
				from #tmpHours H2
				where H2.Row >= H1.Row
			 )  as [AVG_TX/HR]
	from #tmpHours H1
	drop table #tmpHours

Thanks for your help.

Larry