CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Zoom1992
Zoom1992

Reputation: 73

Select data within specified time periods in a day SQL Query

I need to get items received within different specified time periods within a day. An output like this; SQL Output

Do anyone have idea how i can implement a sql query to return result table like this, Thanks in advance.

Upvotes: 0

Views: 1554

Answers (1)

user8764477
user8764477

Reputation:

You can do this:

SELECT Item,
  SUM(CASE WHEN CAST(DateTimeCol AS Time) >= '08:00:00' AND CAST(DateTimeCol AS Time) < '10:00:00' THEN 1 ELSE 0 END) AS [8AM-10AM],
  SUM(CASE WHEN CAST(DateTimeCol AS Time) >= '10:00:00' AND CAST(DateTimeCol AS Time) < '12:00:00' THEN 1 ELSE 0 END) AS [8AM-10AM],
  SUM(CASE WHEN CAST(DateTimeCol AS Time) >= '12:00:00' AND CAST(DateTimeCol AS Time) < '14:00:00' THEN 1 ELSE 0 END) AS [10AM-12AM],
  SUM(CASE WHEN CAST(DateTimeCol AS Time) >= '14:00:00' AND CAST(DateTimeCol AS Time) < '16:00:00' THEN 1 ELSE 0 END) AS [12PM-2PM],
  SUM(CASE WHEN CAST(DateTimeCol AS Time) >= '16:00:00' THEN 1 ELSE 0 END)                                            AS [>4PM]
FROM table1
GROUP BY Item;

Sample Results:

|   Item | 8AM-10AM | 10AM-12AM | 12PM-2PM | 2AM-4PM | >4PM |
|--------|----------|-----------|----------|---------|------|
| Item 1 |        2 |         4 |        1 |       0 |    0 |
| Item 2 |        0 |         0 |        1 |       2 |    2 |

Update:

You can use the PIVOT table operator with temporary table with all the time ranges as following:

SELECT *
FROM
(
  SELECT
    Item,
    Alias
  FROM Table1
  INNER JOIN Ranges  ON CAST(DateTimeCol AS TIME) >= [Start] 
                    AND CAST(DateTimeCol AS TIME) < [End]
) AS t
PIVOT
(
  COUNT(Alias)
  FOR Alias IN([8AM-10AM], [10AM-12AM], [12PM-2PM],[2PM-4PM], [>4PM])
 ) AS p;

Upvotes: 3

Related Questions