Find duration in minutes between dataset where value resets back again

Transact-SQL
1 
--DROP TABLE Taco_Val;
CREATE TABLE Taco_Val
(
    ID          INT IDENTITY(1,1),
    AuditID     VARCHAR(5),
    CreditID    VARCHAR(10),
    TS          DATETIME,
    Val         BIT
);

INSERT INTO Taco_Val
VALUES
 ('a1', 1, '2018-08-09 19:24:39.823',   1),-->Seq started
 ('ac', 1, '2018-08-09 20:53:07.273',   0),
 ('as', 1, '2018-08-09 21:04:40.670',   0),
 ('a9', 1, '2018-08-09 21:14:17.660',   1),-->Another Seq
 ('av', 1, '2018-08-09 21:38:56.910',   1),
 ('ad', 1, '2018-08-09 21:48:46.180',   1),
 ('an', 1, '2018-08-09 22:00:15.650',   0),
 ('a4', 1, '2018-08-09 22:08:26.517',   1),-->Another Seq
 ('a8', 1, '2018-08-09 22:16:16.253',   0),
 ('a3', 1, '2018-08-09 22:16:24.247',   1),-->Another Seq
 ('ai', 1, '2018-08-09 22:18:59.143',   1),
 ('a3', 1, '2018-08-09 22:42:48.780',   1),
 ('ao', 1, '2018-08-09 22:51:21.117',   1),
 ('am', 1, '2018-08-09 23:01:13.777',   0),
 ('ac', 1, '2018-08-09 23:07:13.237',   0),
 ('ab', 1, '2018-08-09 23:16:38.257',   0),
 ('ay', 1, '2018-08-10 15:12:02.473',   1),-->Another Seq
 ('ae', 1, '2018-08-10 15:20:54.263',   0);

--Need the following output

AuditID CreditID    StartTS            Val  Duration
a1      1           2018-08-09 19:24    1   100
a9      1           2018-08-09 21:14    1   46
a4      1           2018-08-09 22:08    1   8
a3      1           2018-08-09 22:16    1   60
ay      1           2018-08-10 15:12    1   8

Starting with row of Val='1's (if the Val='1's are continuous then ignore the rows but count the duration elapsed in minutes on TS until the last continuous '0's ends)

Start again on next row of Val = '1'

2

This is most efficiently done using the "gaps and islands pattern". See for example here

If you get stuck, reply, and someone on the forum can help you out.

3

Hello SATISD9X,

Try this..

--DROP TABLE #Taco_Val;
if OBJECT_ID('tempDb..#Taco_Val') is not null drop table #Taco_Val

CREATE TABLE #Taco_Val
(
    ID          INT IDENTITY(1,1),
    AuditID     VARCHAR(5),
    CreditID    VARCHAR(10),
    TS          DATETIME,
    Val         BIT
);

INSERT INTO #Taco_Val
VALUES
 ('a1', 1, '2018-08-09 19:24:39.823',   1),-->Seq started
 ('ac', 1, '2018-08-09 20:53:07.273',   0),
 ('as', 1, '2018-08-09 21:04:40.670',   0),
 ('a9', 1, '2018-08-09 21:14:17.660',   1),-->Another Seq
 ('av', 1, '2018-08-09 21:38:56.910',   1),
 ('ad', 1, '2018-08-09 21:48:46.180',   1),
 ('an', 1, '2018-08-09 22:00:15.650',   0),
 ('a4', 1, '2018-08-09 22:08:26.517',   1),-->Another Seq
 ('a8', 1, '2018-08-09 22:16:16.253',   0),
 ('a3', 1, '2018-08-09 22:16:24.247',   1),-->Another Seq
 ('ai', 1, '2018-08-09 22:18:59.143',   1),
 ('a3', 1, '2018-08-09 22:42:48.780',   1),
 ('ao', 1, '2018-08-09 22:51:21.117',   1),
 ('am', 1, '2018-08-09 23:01:13.777',   0),
 ('ac', 1, '2018-08-09 23:07:13.237',   0),
 ('ab', 1, '2018-08-09 23:16:38.257',   0),
 ('ay', 1, '2018-08-10 15:12:02.473',   1),-->Another Seq
 ('ae', 1, '2018-08-10 15:20:54.263',   0);
 
 go
 
 ;with cte
 as
 (
 
 select a.* 
 , 1 [grp]
 from #Taco_Val a 
 where a.id = 1
 
 union all
 select a.* 
 ,(case when a.val = 1 and b.Val = 0  then b.grp + 1 else b.grp end)
 from #Taco_Val a 
, cte b
where a.ID = b.ID + 1
		
 
 )
 
 select top 1 with ties a.AuditID
 ,a.CreditID 
 ,convert(varchar(16),a.TS, 120)  [StartTS]
 ,a.Val 
 ,DATEDIFF(minute,a.Ts,b.maxTs) [Duration]
 
 from cte a
 outer apply (select MAX(b.TS) [maxTs]     from cte b
 where b.grp = a.grp
 ) b
 where a.Val = 1
 order by ROW_NUMBER() over(partition by grp order by id) 
 
 go
 
drop table #Taco_Val;