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

Question

For the table:

create table #orders (
    orderid int,
    orderdatetime datetime
)

What is the best way to write sql to display a report containing the count of orders from the current and previous 24 hours, total orders for the current 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 am wondering if this can be effectively collapsed into a single sql using analytic functions, or if 4 sql statements generating 4 groups of data are the only (or best) way.

Also, given hourly / daily / weekly grouping, how to do it on sql server? Every now and then it seems like a pain in the ass every time I have to do something like that with them ...

Ideas? Put the SSAS in the 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 COUNTby day, week and month in one request.

Weekly swaps will have a column NULLin the column DATEPART(day, orderdatetime), in the accumulators of the month will be NULLin the columns DATEPART(day, orderdatetime)and DATEPART(week, orderdatetime).

To do this for every hour, day, week or month from the current one without spaces, 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 can run four selections from the dummy table or identity table, which consists of one row.

You may 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

datepart, rollup, , , . , , - ...

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

, . , sql .

EDIT1: , SQL Server 2005 , SQL Server 2008 . , mapreduce rdbms, Oracle Sql Server. , ! 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 on a single line, 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 might want to fine-tune date criteria to get the appropriate behavior.

For a few lines, take the results above and wrap them with UNPIVOT or CASE .. CROSS JOIN.