Cumulative duration between value resets

--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),--> 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'

I hope this is much clear

This is usually called a GROUPING AND WINDOWS solution.

Basically you set reset points according some rules, then you set up groups by SUMming reset points and finally use aggregated functions to get the desired value.

;WITH reset AS
(
SELECT AuditID, 
       CreditID, 
       TS, 
       Val,
       /* Next CASE returns 1 when:
             a) It is the first row of a partition
             b) Current Val=1 and previous = 0
          Otherwise it returns 0
       */
       CASE WHEN Val=1 AND COALESCE(LAG(Val) OVER (PARTITION BY CreditID ORDER BY CreditID, TS), 0) = 0
            THEN 1 ELSE 0 END AS Rst        
FROM   Taco_Val
)
  , [group] AS
    (
    SELECT AuditID, 
           CreditID, 
           TS, 
           Val,
           /* Next SUM generates a cumulative sum of reset points.
              That is a correlative number for each partition
           */ 
           SUM(Rst) OVER (PARTITION BY CreditID ORDER BY CreditID, TS) AS Grp
    FROM   reset
    )
    SELECT 
           /* Next select returns, of every group, AuditID corresponding to
              the row with the minimum TS
           */ 
           (SELECT AuditID FROM [group] WHERE CreditID = g.CreditID AND TS = MIN(g.TS)) AuditID, 
           CreditID,
           MIN(TS) as StartTS, 
           1 as Val,
           DATEDIFF(MINUTE, MIN(TS), MAX(TS)) as Duration
    FROM   [group] g
    GROUP BY CreditID, Grp;
GO

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

db<>fiddle here

You'll find a brief explanation of every CTE on the previous fiddle.

StackExchange Database Administrators Q#221392, answer score: 7