Count number of occurrences before a certain value-CodePudding

I am trying to count of how many times "Agent" rows appears with 2 or more "Threading" on top of it.

In the below example, the count is 2 as row 3 (Agent) has 2 threading on top and row 7 (Agent) has got 3 threading on top. The order is determined by the date column.

Any idea how to get the count?

Below is code I am using:

DECLARE @INFO AS TABLE
(
    Process VARCHAR(10) ,
    Content VARCHAR(100) ,
    Date DATETIME
)

INSERT INTO @INFO
(
    Process,
    Content ,
    Date
)
VALUES
('Threading', 'Content A', GETDATE() - 9), ('Threading', 'Content B', GETDATE() - 8), ('Agent', 'Content C', GETDATE() - 7), ('Threading', 'Content D', GETDATE() - 6), ('Threading', 'Content E', GETDATE() - 5), ('Threading', 'Content F', GETDATE() - 4), ('Agent', 'Content G', GETDATE() - 3), ('Threading', 'Content H', GETDATE() - 2), ('Agent', 'Content I', GETDATE() - 1)

SELECT * FROM @INFO AS i

-- Result it 2.

CodePudding user response：

One method would be to use a windowed COUNT to put the rows into groups, based on how many times 'Agent' has appeared prior to the row. Then you can COUNT how many 'Threading's are in each group, and then finally COUNT how many of those groups have a value of 2 or more:

WITH Grps AS (
    SELECT Process,
           Date,
           COUNT(CASE Process WHEN 'Agent' THEN 1 END) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Grp
    FROM @INFO),
Counts AS(
    SELECT COUNT(CASE Process WHEN 'Threading' THEN 1 END) AS Threadings,
           Grp
    FROM Grps
    GROUP By Grp)
SELECT COUNT(*)
FROM Counts
WHERE Threadings >= 2;

CodePudding user response：

Found a solution. I am using the LAG function to get the second last and last process per row.

SELECT  * , LAG(i.Process, 1) OVER ( ORDER BY i.Date) lastProcess,  LAG(i.Process, 2) OVER ( ORDER BY i.Date) secondlast
FROM    @INFO AS i