none
how to sum up the quantity of orders at different interval times RRS feed

  • 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.

    Thanks in advance.

    Jack

    Tuesday, January 7, 2020 3:45 PM

Answers

  • 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(
    select dateadd(mi,
    (datediff(mi,convert(varchar(10),dateadd(ss,1,ordertimes),120),dateadd(ss,1,ordertimes))/15)*15 
          ,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 Lily Lii Friday, January 10, 2020 9:03 AM
    • Marked as answer by JackandRobinson 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 
    ,Dateadd(minute, ceiling(datepart(minute,dt) /15.)*15 % 60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0))
    FROM MYTABLE
    GROUP BY  Dateadd(minute, ceiling(datepart(minute,dt) /15.)*15 % 60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0))
     
    
    drop table mytable

    Tuesday, January 7, 2020 3:57 PM
    Moderator
  • 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(
    select dateadd(mi,
    (datediff(mi,convert(varchar(10),dateadd(ss,1,ordertimes),120),dateadd(ss,1,ordertimes))/15)*15 
          ,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 Lily Lii Friday, January 10, 2020 9:03 AM
    • Marked as answer by JackandRobinson 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