I need the time difference between two times in minutes, and i want the result to be grouped by the-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.