How can I run calculations on batches of entries in a table based on other columns?

Question

I have a SQL table that looks kind of like the following

entry_ID    type_of_entry   time_of_entry
1           0               12:00
2           0               12:05
3           1               12:15
4           0               15:14
5           1               15:30

What I'm hoping to do is extract the times taken between the first 0 in the type of entry chain, and the 1 to end the chain. The number of 0 type_of_entry rows between the 1s will vary.

So for this table I would want:

TIMEDIFF(12:15 , 12:00) = 15 mins
TIMEDIFF(15:30 , 15:14) = 16 mins

How can I get the groupings I want, ideally without looping through the table with a FOR EACH?

SSMS (SQL Server Management Studio) doesn't work with MySQL, so why have you tagged both? — Thom A, Jun 16 '23 at 14:18
Then you should tag SQL Server, not SSMS; SSMS is just an IDE like environment so tagging it for your (R)DBMS is like tagging [[tag:visual-studio]] for your programming language. As you're using SQL Server 16, you should tag [[tag:sql-server-2022]] too when you correct the post. — Thom A, Jun 16 '23 at 14:25

Thom A · Accepted Answer · 2023-06-16T15:12:01.863

Seems like what you have here is a gaps and islands problem. One method to put the data into groups is to use COUNT to count the number of 1 entries prior to the current row. Then you can get the difference in minutes for the MAX and MIN entry times in each of those groups:

WITH Grps AS(
    SELECT entry_ID,
           type_of_entry,
           time_of_entry,
           COUNT(CASE type_of_entry WHEN 1 THEN 1 END) OVER (ORDER BY entry_ID 
                                                             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS grp
    FROM (VALUES(1,0,CONVERT(time(0),'12:00:00')),
                (2,0,CONVERT(time(0),'12:05:00')),
                (3,1,CONVERT(time(0),'12:15:00')),
                (4,0,CONVERT(time(0),'15:14:00')),
                (5,1,CONVERT(time(0),'15:30:00')))V(entry_ID,type_of_entry,time_of_entry))
SELECT DATEDIFF(MINUTE,MIN(time_of_entry), MAX(time_of_entry)) AS TimeDiff
FROM Grps
GROUP BY grp
ORDER BY MIN(entry_ID);

score 0 · Answer 2 · answered Jun 16 '23 at 15:01

This is a gap and island problem, we need to group the data in such a way that every two successive rows belong to the same group. This can be achieved by using the window function sum(), once the data is grouped, apply the datediff function to calculate the time difference between consecutive rows within each group :

The first cte is used to establish an initial grouping of the data.

The second cte2, is used to identify the first occurrence of type 0 and the last occurrence of type 1.

cte3 is used to generate the final grouping based on the previous CTEs.

with cte as (
  select *, sum(type_of_entry) over (order by entry_ID ) as rn
  from mytable
),
cte2 as (
  select type_of_entry, min(entry_ID) as entry_ID
  from cte 
  where type_of_entry = 0
  group by type_of_entry, rn
  union all
  select type_of_entry, max(entry_ID) as entry_ID
  from cte 
  where type_of_entry = 1
  group by type_of_entry, rn
),
cte3 as (
  select *, row_number() over (order by entry_ID) as rn
  from cte2
)
select DATEDIFF(minute, CONVERT(TIME, min(time_of_entry)), CONVERT(TIME, max(time_of_entry)))
from cte3 c
inner join mytable t on t.entry_ID = c.entry_ID
group by floor((rn-1)/2)

Demo here

How can I run calculations on batches of entries in a table based on other columns?

2 Answers2