# how to sum up the quantity of orders at different interval times

• ### Question

• This is a tidy version of my table #ordertime as an example：

productid    productname     ordertimes

-----------------------------------------------------

1                 a                  2020-1-1 11:01:00

2                 b                  2020-1-1 11:03:00

3                 c                  2020-1-1 11:05:00

4                 d                  2020-1-1 11:09:00

5                 e                  2020-1-1 11:17:00

6                 f                  2020-1-1 11:19:00

7                 g                  2020-1-1 11:29:00

8                 h                  2020-1-1 11:37:00

9                 i                  2020-1-1 11:45:00

.................

the output table should be:

interval                             quantity

-------------------------------------------

2020-01-01 11:00:00           4

2020-01-01 11:15:00           3

2020-01-01 11:30:00           1

2020-01-01 11:45:00           1

...............

I have tried to use DATENAME and DATEADD, but I just can't figure it out.

Jack

Tuesday, January 7, 2020 3:45 PM

• ```DECLARE @OrderTimes TABLE (
productid int,
ordertimes datetime
);
INSERT INTO @OrderTimes VALUES
(1, '2020-1-1 11:01:00'), (2, '2020-1-1 11:03:00'),
(3, '2020-1-1 11:05:00'), (4, '2020-1-1 11:09:00'),
(5, '2020-1-1 11:17:00'), (6, '2020-1-1 11:19:00'),
(7, '2020-1-1 11:29:00'), (8, '2020-1-1 11:37:00'),
(9, '2020-1-1 11:45:00');

SELECT CAST(t.[Date] AS varchar(10)) + ' ' + RIGHT('00' + CAST([Hour] AS varchar(2)), 2) + ':' + [MinuteRange] + ':00' AS interval, COUNT(*) AS quantity
FROM (
SELECT
CAST(ordertimes AS date) AS [Date],
DATEPART(hour, ordertimes) AS [Hour],
CASE
WHEN DATEPART(minute, ordertimes) < 15 THEN '00'
WHEN DATEPART(minute, ordertimes) < 30 THEN '15'
WHEN DATEPART(minute, ordertimes) < 45 THEN '30'
ELSE '45'
END AS [MinuteRange]
FROM @OrderTimes
) AS t
GROUP BY [Date], [Hour], [MinuteRange];```

A Fan of SSIS, SSRS and SSAS

Tuesday, January 7, 2020 4:38 PM
• Hi Jack,

```create table #ordertime(
productid int,
productname nvarchar(100),
ordertimes datetime)

insert #ordertime values
(1,'a','2020-1-1 11:01:00'),
(2,'b','2020-1-1 11:03:00'),
(3,'c','2020-1-1 11:05:00'),
(4,'d','2020-1-1 11:09:00'),
(5,'e','2020-1-1 11:17:00'),
(6,'f','2020-1-1 11:19:00'),
(7,'g','2020-1-1 11:29:00'),
(8,'h','2020-1-1 11:37:00'),
(9,'i','2020-1-1 11:45:00')

;with cte1 as(
,convert(varchar(10),ordertimes,120)) as interval
from  #ordertime )
select interval, count(interval) as quantity
from cte1
group by interval```

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Friday, January 10, 2020 9:03 AM
• Marked as answer by Friday, January 10, 2020 10:38 AM
Wednesday, January 8, 2020 1:53 AM

### All replies

• ```CREATE TABLE mytable(
productid   VARCHAR(30)
,productname INTEGER
,ordertimes  VARCHAR(1)
,DT      DATETIME
);
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,1,'a','2020-1-1 11:01:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,2,'b','2020-1-1 11:03:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,3,'c','2020-1-1 11:05:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,4,'d','2020-1-1 11:09:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,5,'e','2020-1-1 11:17:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,6,'f','2020-1-1 11:19:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,7,'g','2020-1-1 11:29:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,8,'h','2020-1-1 11:37:00');
INSERT INTO mytable(productid,productname,ordertimes,DT) VALUES (NULL,9,'i','2020-1-1 11:45:00');

select COUNT(*) CNT
FROM MYTABLE

drop table mytable```

Tuesday, January 7, 2020 3:57 PM
• ```DECLARE @OrderTimes TABLE (
productid int,
ordertimes datetime
);
INSERT INTO @OrderTimes VALUES
(1, '2020-1-1 11:01:00'), (2, '2020-1-1 11:03:00'),
(3, '2020-1-1 11:05:00'), (4, '2020-1-1 11:09:00'),
(5, '2020-1-1 11:17:00'), (6, '2020-1-1 11:19:00'),
(7, '2020-1-1 11:29:00'), (8, '2020-1-1 11:37:00'),
(9, '2020-1-1 11:45:00');

SELECT CAST(t.[Date] AS varchar(10)) + ' ' + RIGHT('00' + CAST([Hour] AS varchar(2)), 2) + ':' + [MinuteRange] + ':00' AS interval, COUNT(*) AS quantity
FROM (
SELECT
CAST(ordertimes AS date) AS [Date],
DATEPART(hour, ordertimes) AS [Hour],
CASE
WHEN DATEPART(minute, ordertimes) < 15 THEN '00'
WHEN DATEPART(minute, ordertimes) < 30 THEN '15'
WHEN DATEPART(minute, ordertimes) < 45 THEN '30'
ELSE '45'
END AS [MinuteRange]
FROM @OrderTimes
) AS t
GROUP BY [Date], [Hour], [MinuteRange];```

A Fan of SSIS, SSRS and SSAS

Tuesday, January 7, 2020 4:38 PM
• Hi Jack,

```create table #ordertime(
productid int,
productname nvarchar(100),
ordertimes datetime)

insert #ordertime values
(1,'a','2020-1-1 11:01:00'),
(2,'b','2020-1-1 11:03:00'),
(3,'c','2020-1-1 11:05:00'),
(4,'d','2020-1-1 11:09:00'),
(5,'e','2020-1-1 11:17:00'),
(6,'f','2020-1-1 11:19:00'),
(7,'g','2020-1-1 11:29:00'),
(8,'h','2020-1-1 11:37:00'),
(9,'i','2020-1-1 11:45:00')

;with cte1 as(
,convert(varchar(10),ordertimes,120)) as interval
from  #ordertime )
select interval, count(interval) as quantity
from cte1
group by interval```

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Friday, January 10, 2020 9:03 AM
• Marked as answer by Friday, January 10, 2020 10:38 AM
Wednesday, January 8, 2020 1:53 AM
• Hi Jack,

Do the answers above help you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, January 9, 2020 9:25 AM