SQL Group for different periods of time day

Question

I need to request a sales table, and the breakdown table will be displayed in the final table for different periods of time during a given day.

For example, a table has the following fields:

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

I would like to create a table that looks like this:

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

Any help on this would be greatly appreciated. Thanks

Answer 1

Suppose SQL Server is below. If this logic is most likely not applicable to your RDBMS, but probably another function is to get the time part from the date and time, and the BETWEEN behavior may be different (this is a comprehensive range in SQL Server).

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*/ 

Note. Previous versions of SQL Server require a few more hoops to get only a date from a date and time. e.g. CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME (From here )

Answer 2

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

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

(That the PostgreSQL interval syntax, btw ... may be non-standard.) But there are probably more elegant ways to do this.