Group rows based on the current value starting from the next row

Question

I need to increment group number for the all next rows with the value <> z. z would mean that all the next rows according to b will have the same group number.

CREATE TABLE #tmp
(
    a CHAR(1)
  , b INT
);

INSERT INTO #tmp (a
                , b)
VALUES ('a', 1)
     , ('b', 2)
     , ('z', 3)
     , ('c', 4)
     , ('z', 5)
     , ('z', 6)
     , ('d', 7);

SELECT       t.a
           , t.b
           , SUM(v.is_z) OVER (ORDER BY t.b ROWS UNBOUNDED PRECEDING) - ROW_NUMBER() OVER (ORDER BY t.b) group_nbr
  FROM       #tmp                                                 AS t
 CROSS APPLY (SELECT CASE WHEN a = 'z' THEN 2 ELSE 1 END AS is_z) AS v
 ORDER BY 2;

DROP TABLE #tmp;

In my query the group is incremented starting from the row with the value of z, but I need to start incrementing it for the next row.

Expected output:

Answer 1

A solution is to increment by 1 each time you find a z, then LAG the result by 1 row at the end.

;WITH CumulativeZ AS
(
    SELECT
        T.*,
        CumulativeZ = SUM(CASE WHEN T.a = 'z' THEN 1 ELSE 0 END) OVER(ORDER BY T.b ASC)
    FROM
        #tmp AS T
)
SELECT
    C.a,
    C.b,
    C.CumulativeZ,
    [Group] = LAG(C.CumulativeZ, 1, 0) OVER (ORDER BY C.b ASC)
FROM
    CumulativeZ AS C

Result:

a   b   CumulativeZ Group
a   1   0           0
b   2   0           0
z   3   1           0
c   4   1           1
z   5   2           1
z   6   3           2
d   7   3           3

Please note that the LAG function is for SQL Server 2012+. You can mimic it's functionality with a ROW_NUMBER on version 2008+.