What is the most efficient way to create an order count summary by hour, day, month in SQL Server 2005?

Question

Given a table:

create table #orders (
    orderid int,
    orderdatetime datetime
)

What is the best way to write sql to output a report containing the count of orders from the current and previous 24 hours, total orders for the current day and previous 7 days, total orders for the current week and previous 4 weeks, and total orders for the month and previous 6 months?

I'm wondering if this can be efficiently rolled up into a single sql using analytical functions, or if 4 sql statements generating the 4 groups of data is the only (or best) way.

Also, given the hourly/day/week grouping, how would one do that in sql server? Datetimes seem to be a pain in the ass everytime I have to do something like this with them...

Ideas? Put into a SSAS cube and do it from there maybe?

Answer 1

SELECT  DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime), COUNT(*)
FROM    #orders
GROUP BY
        DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime) WITH ROLLUP

This will group the COUNT's by day, week and month in a single query.

The week rollups will have a NULL in DATEPART(day, orderdatetime) column, the month rollups will have a NULL in both DATEPART(day, orderdatetime) and DATEPART(week, orderdatetime) columns.

To make it for every hour, day, week or month from the current without gaps, use CTE's:

WITH    q_hours AS
        (
        SELECT  0 AS col_hour
        UNION ALL
        SELECT  col_hour + 1
        FROM    q_hours
        WHERE   col_hour < 22
        ),
        q_days AS
        (
        SELECT  0 AS col_day
        UNION ALL
        SELECT  col_day + 1
        FROM    q_days
        WHERE   col_day < 31
        ),
        q_months AS
        (
        SELECT  0 AS col_month
        UNION ALL
        SELECT  col_month + 1
        FROM    q_months
        WHERE   col_month < 12
        )
SELECT  col_month, col_day, col_hour, COUNT(orderid)
FROM    q_hours
CROSS JOIN
        q_days
CROSS JOIN
        q_months
LEFT JOIN
        #orders
ON      DATEDIFF(month, orderdatetime, GETDATE()) = col_month
        AND DATEDIFF(day, orderdatetime, GETDATE()) % 31 = col_day
        AND DATEDIFF(hour, orderdatetime, GETDATE()) % 24 = col_hour
GROUP BY
        col_month, col_day, col_hour WITH ROLLUP
HAVING  (
        col_month = 0
        AND col_day = 0
        AND col_hour IS NOT NULL
        ) -- all hours within 24 hours from now
        OR
        (
        col_month = 0
        AND col_day <= 7
        AND col_hour IS NULL
        ) -- all days within 7 days from now
        OR
        (
        col_month <= 6
        AND col_day IS NULL
        AND col_hour IS NULL
        ) -- all months within 6 months from now

Answer 2

You could run the four selects from a "dummy table" or an "identity" table that consists of a single row.

You could have:

SELECT
    (<query count of orders current/prev 24 hours>) as <name1>,
    (<total orders current + 7 days>) as <name2>,
    (<total orders current week + 4 weeks>) as <name3>,
    (<total orders month + 6 months>) as <name4>
FROM
<IDENTITY table>;

Answer 3

Because you want different timeframes for each datepart type, using a single query with rollup probably won't give you what you want. I'd consider just unioning them all together similar to something like this...

SELECT  DatePartValue = DATEPART(HH, orderdatetime), 
        Type = 'Hourly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(HH, -25, GETDATE())
GROUP BY DATEPART(HH, orderdatetime)
UNION 
SELECT  DATEPART(DD, orderdatetime), 
        Type = 'Daily',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(DD, -8, GETDATE())
GROUP BY DATEPART(DD, orderdatetime)
UNION 
SELECT  DATEPART(WEEK, orderdatetime), 
        Type = 'Weekly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(WEEK, -5, GETDATE())
GROUP BY DATEPART(WEEK, orderdatetime)
ORDER BY Type, DatePartValue
UNION 
SELECT  DATEPART(MM, orderdatetime), 
        Type = 'Monthly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(MM, -7, GETDATE())
GROUP BY DATEPART(MM, orderdatetime)
ORDER BY Type, DatePartValue

Answer 4

I think you want grouping sets. I've understand that sql server supports grouping sets.

EDIT1: I've read that sql server 2005 doesn't support grouping sets but sql server 2008 does. Here an interesting read on a presumed but not existing difference between mapreduce and an rdbms like Oracle and Sql Server. Please read the comments too!! http://www.data-miners.com/blog/2008/01/mapreduce-and-sql-aggregations.html

Answer 5

SELECT     
    sum(case when orderdatetime between GetDate() - 1 and GetDate() then 1 else 0 end) as Current24Hours,
    sum(case when orderdatetime between GetDate() - 2 and GetDate() - 1 then 1 else 0 end) as Previous24Hours,
    sum(case when orderdatetime between GetDate() - 7 and GetDate() then 1 else 0 end) as Current7Days,
    sum(case when orderdatetime between GetDate() - 14 and GetDate() - 7 then 1 else 0 end) as Previous7Days,
    sum(case when DATEDIFF (m, OrderDate, @now) <= 1 then 1 else 0 end) as PreviousMonth,
    sum(case when DATEDIFF (m, OrderDate, @now) <= 6 then 1 else 0 end) as PreviousSixMonths
FROM orders

Answer 6

For results in one row, something like this:

select
  orders_day   = sum(case when datediff(hour,orderdatetime,getdate())  < 24 then 1 else 0 end)
, orders_week  = sum(case when datediff(day,orderdatetime,getdate())   < 7  then 1 else 0 end)
, orders_month = sum(case when datediff(week,orderdatetime,getdate())  < 4  then 1 else 0 end)
, orders_half  = sum(case when datediff(month,orderdatetime,getdate()) < 6  then 1 else 0 end)
from #orders

You may want to fine tune the date criteria to get appropriate behavior.

For multiple rows, take the results above and transpose it with UNPIVOT or CASE .. CROSS JOIN.