Avg Transactions Per Hour Based on Timestamp 1

[larrydavid]

Hello, I have a small table for Order Processing which has an Order#, Timestamp and Status column. What I'm trying to do is achieve the following in one summary level record for real time reporting, based on time intervals:

1.Count of Orders
2.AVG Orders/HR
3.Minimum Process (Turnaround) Time
4.Maximum Process (Turnaround) Time
5.First Order Entered
6.Last (most recent) Order Entered

The idea is this will tell me at any given time where our order entry folks are at with their orders during the day.

I've been playing around with different solutions and have this so far, but it isn't giving me everything.

So for the Averaging/HR:

SELECT
   [Hour],
   HourlyAverage = AVG(c)
FROM
   (SELECT
       Timestamp,
       [Hour] = DATEPART(HOUR, Timestamp),
       c = COUNT(*)
   FROM
       Orders
   WHERE Timestamp >= '20060101'
   GROUP BY
       Timestamp,
       DATEPART(HOUR, Timestamp)
   ) x

For the counts:

For "per day":

SELECT   DATEADD(day, DATEDIFF(day, '20000101', Timestamp), '20000101')
AS OrderDay, COUNT(*) AS OrderCount
FROM     Orders
GROUP BY DATEDIFF(day, '20000101', Timestamp);

For "per hour":

SELECT   DATEADD(hour, DATEDIFF(hour, '20000101', Timestamp),
'20000101') AS OrderHour, COUNT(*) AS OrderCount
FROM     Orders
GROUP BY DATEDIFF(hour, '20000101', Timestamp);

I'm struggling with items 3-6 and how to put it all together,
Any help greatly appreciated.

Thanks,
Larry

 

[markros]

If you're using SQL Server 2005+, then you can do everything with one query using WINDOWing functions (for aggregates and ROW_NUMBER() to get the last).



PluralSight Learning Library

 

[SimonSellick]

You might find this article useful if you aren't familiar with OVER, window and ranking functions:

http://www.simple-talk.com/sql/learn-sql-server/working-with-window-functions-in-sql-server/.

 

[larrydavid]

Hi Simon, Thank you for going a little extra with the link. I appreciate when people don't assume I know what they know.

Much appreciated.
Larry