sql select values on Continuous period of time

Question

I have a table with below structure:

id INT, order_id INT, datex DATE, timex TIME

For example I want to select count of order ID's from 2017-10-24 to 2017-10-26 in specific periods of time. Like 2:0:0 - 4:0:0,4:0:0 - 6:0:0 etc and make a result like below:

period        | ordersID's
2:0:0 - 4:0:0 | 5
4:0:0 - 6:0:0 | 8

Can I do this by just a query or I should use other ways?

Answer 1

First thing is your time periods are overlapping. It is not clear that if orders occur at 4 what period they should belong in. For my examples below I am assuming orders occurring on the hour at the end of the period belong to the next period.

One way to solve this with just a query is to use a case statement in your SELECT and GROUP BY clauses.

CREATE TABLE #test
(
    id int identity(1,1),
    order_id int,
    datex date,
    timex time
)

INSERT INTO #test VALUES
(5, '10/24/2017', '02:01'),
(5, '10/24/2017', '04:01'),
(6, '10/25/2017', '03:01'),
(7, '10/26/2017', '05:01'),
(8, '10/27/2017', '02:01')

SELECT
    CASE
        WHEN timex >= '02:00' AND timex < '04:00' THEN '2:0:0 - 4:0:0'
        WHEN timex >= '04:00' AND timex < '06:00' THEN '4:0:0 - 6:0:0'
        END AS 'periodLabel', 
    COUNT(order_id) AS 'OrderCount'
FROM
    #test
WHERE
    datex >= '10/24/2017'
    AND datex <= '10/26/2017'
GROUP BY
    CASE
        WHEN timex >= '02:00' AND timex < '04:00' THEN '2:0:0 - 4:0:0'
        WHEN timex >= '04:00' AND timex < '06:00' THEN '4:0:0 - 6:0:0'
        END

Another way to solve this is to create a table with your time periods in it and join it. I prefer this solution because it is easier to maintain.

CREATE TABLE #timePeriod
(
    periodLabel varchar(50),
    periodBegin time,
    periodEnd time
)

INSERT INTO #timePeriod VALUES
('02:00 - 04:00', '02:00', '04:00'),
('04:00 - 06:00', '04:00', '06:00')

SELECT
    B.periodLabel,
    COUNT(order_id) AS 'OrderCount'
FROM
    #test AS A
    JOIN #timePeriod AS B
        ON A.timex >= B.periodBegin
            AND A.timex < B.periodEnd
WHERE
    A.datex >= '10/24/2017'
    AND A.datex <= '10/26/2017'
GROUP BY
    B.periodLabel