I need the time difference between two times in minutes, and i want the result to be grouped by the difference and number of count

Question

I have a table called Order_Timing with 2 cloumns named StartTime and EndTime as Shown below. I would like to get the difference in minutes and the count.

start time	End Time
2022-03-14 09:28:42.250	2022-03-14 09:29:23.693
2022-03-14 09:28:42.250	2022-03-14 09:30:40.150
2022-03-14 09:37:59.577	2022-03-14 09:38:04.730
2022-03-14 09:38:26.097	2022-03-14 09:38:38.583

Below is my expected result

Minutes	Count
0 - 1 mins	1
1 - 2 mins	2
2 - 3 mins	1

SELECT StartTime, EndTime, DATEDIFF(MINUTE, StartTime , EndTime) AS Minute FROM Order_Timing. But this code does not give a count as stated in my initial result i wish to achieve. — Pumpin101, Mar 14 '22 at 12:44

score 0 · Answer 1 · edited Mar 14 '22 at 14:12

0

You need to find the count by grouping the Minute Difference value which you have obtained in your query.

SELECT CASE 
        WHEN minute_diff = 0
            THEN '0-1 min'
        WHEN minute_diff = 1
            THEN '1-2 min'
        WHEN minute_diff = 2
            THEN '2-3 min'
        END AS Minutes
    ,count(*) AS [count]
FROM (
    SELECT [start time]
        ,[end time]
        ,DATEDIFF(MINUTE, [start time], [end time]) AS minute_diff
    FROM Order_Timing
    ) a
GROUP BY minute_diff
ORDER BY 1

You can tweak the case statement as per your need.

edited Mar 14 '22 at 14:12

Dharman

30,962
25
85
135

answered Mar 14 '22 at 14:06

Prakaash

89
7

This really solved my problem but what if i want to add another column which will be in percentage of the count e.g | Minutes | Count | % | – Pumpin101 Mar 14 '22 at 21:38
See this https://stackoverflow.com/questions/37303779/sql-calculate-percentage-on-countcolumn It will help you to achieve it – Prakaash Mar 15 '22 at 03:41

score 0 · Accepted Answer · answered Mar 14 '22 at 14:18

0

select count(diff) 
from (select datediff(minute,start,enddate) as diff from [table])a 
group by diff

answered Mar 14 '22 at 14:18

Nikos Papaefstathiou

24
2

I need the time difference between two times in minutes, and i want the result to be grouped by the difference and number of count

2 Answers2