SQL Group by different time periods with day

Question

hi there,

I need to query a sales table and have the resulting table show the breakdown for different time periods within the given day.

For example, the table has the following fields:

Id (int), EntryDate (datetime), SaleReference (varchar) 

I'd like to produce a table that looks like this:

Date        Sales 9am-12pm   Sales 12pm-3pm   Sales 6pm-9pm
----------  --------------   --------------   -------------
01-01-2010        10               20               6  
02-01-2010        12               16               3  
03-01-2010        43               11               2  

Any help on this would be greatly appreciated. Thanks

Answer 1

Assuming SQL Server below. If not a similar logic will probably apply with your RDBMS but likely a different function to get the hour part from the datetime and the behaviour of BETWEEN may be different too (In SQL Server it is an inclusive range).

SELECT CAST([Date] AS Date) AS [Date],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 9 AND 11 THEN 1 ELSE NULL END)
    AS [Sales 9am-12pm],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 14 THEN 1 ELSE NULL END) 
    AS [Sales 12pm-3pm],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 18 AND 20 THEN 1 ELSE NULL END) 
    AS [Sales 6pm-9pm]
FROM Table
GROUP BY CAST([Date] AS Date) /*For SQL2008*/

NB: Previous versions of SQL Server require a few more hoops to get just the date part out of a datetime. e.g. CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME (From here)

Answer 2

Assuming your database supports this kind of date math, you can say:

CASE WHEN EntryDate - date(EntryDate) >= INTERVAL '9 hours'
      AND EntryDate - date(EntryDate) < INTERVAL '12 hours'
     THEN ...

(That's the PostgreSQL interval syntax, btw... might be nonstandard.) But there are probably more elegant ways of doing it.