1 . ) How to count consecutive values across rows? Start from zero and count cumulatively each time the value repeats. Restart from zero each time the value changes. Example: SomeValue ConsecutiveCount 1 0 1 1 1 2 0 0 1 0 1 1 Attached script creates a db with two tables that shows an attempt to accomplish the above. 2 . ) Query in attached script is obviously incorrect with respect to accomplishing the task stated above. That said, the results returned by the query appear to be inconsistent and unmatchable to any pattern demonstated in the data. For example the following row returned by the query against TableB shows a value of 2 in the SameConsecutiveResponseCount column: 2018-03-14 00:00:00.000 67115991 1 0 2 For the query as written in the attached script, how is the value 2 calculated? What does the value 2 represent? 3 . ) The same query as the attached run against the same data on our production server yields different results. I had to truncate some data to keep the example small, however as shown in the screen cap below the SameConsecutiveResponseCount starts at 3 and increments by 3 for each row. The first three rows on the prod server are exactly the same as what is provided in the example. At a minimum I would expect those rows to return identical results. Why does this occur? 169098-consecutive-values-problem.txt select @@version Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: )

Hi @Wheat, Samuel G (Contractor) For questions 2.) Because you SUM with partition by Response ,which means: " first SUM partition :Response=0" , "second SUM partition :Response=1" sum(case when Response = LastResponse then 1 else 0 end) over( partition by Response order by InterrogationDate) Change order by clause to this: order by SerialNumber, Response,InterrogationDate and you will find the reason.

Another example: with cte1 as ( select * , row_number() over (partition by Response order by InterrogationDate) as Rn from TableB ) , cte2 as ( select * , ( case when (rn-lag(Rn,1,Rn) over (order by InterrogationDate)) <> 1 then Rn else 0 end ) as Delta from cte1 ) select InterrogationDate , SerialNumber , Response , ( case when Delta <> 0 then 0 else Rn - (select max(c.Rn) from cte2 as c where c.Response = cte2.Response and c.Rn = c.Delta and c.Rn < cte2.Rn) end ) as ConsecutiveResponseCount from cte2 order by InterrogationDate ![171627-immagine-2022-02-06-122408.png][1] [1]: /api/attachments/171627-immagine-2022-02-06-122408.png?platform=QnA

How to count consecutive values across rows?

Accepted answer

LiHong-MSFT 10,046 Reputation points

2022-01-28T01:36:18.76+00:00

Hi @Wheat, Samuel G (Contractor)
For questions 2.) Because you SUM with partition by Response,which means: " first SUM partition :Response=0" , "second SUM partition :Response=1"

sum(case when Response = LastResponse then 1 else 0 end) over(partition by Response order by InterrogationDate)

Change order by clause to this:order by SerialNumber, Response,InterrogationDate and you will find the reason.
Please sign in to rate this answer.
Wheat, Samuel G (Contractor) 21 Reputation points

2022-01-28T03:12:11.59+00:00

first SUM partition :Response=0" , "second SUM partition :Response=1"

I don't understand what you mean by this. I understand the window function to sum once - the value being summed depends on the comparison of Response/LastResponse.

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

From the link above:

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

See also this example:

USE AdventureWorks2012; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) *100 AS DECIMAL(5,2))AS "Percent by ProductID" FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); GO Here is the result set. Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each SalesOrderID.

In my case I want to calculate an aggregate by Response ordered by SerialNumber,LastResponse. When Response changes I want to restart the aggragate. Note the phrase computation restarts for each partition. in the documentation cited above. This is the part I don't understand. Why is computation not restarting for each partition?

LiHong-MSFT 10,046 Reputation points

2022-01-28T03:22:54.233+00:00

Why is computation not restarting for each partition?

In fact, your query does restart the computation per partition.
Because there are only 2 partitions :partition 1:Response=0 （Inside the blue rectangle ）and partition 2:Response=1 （Inside the green rectangle ）

Wheat, Samuel G (Contractor) 21 Reputation points

2022-01-28T03:30:16.573+00:00

They are sorted incorrectly in the rectangles. The desired sort is by serialnumber, interrogation date. When sorted by those columns the desired functionality is that computation restarts each time Response changes.

LiHong-MSFT 10,046 Reputation points

2022-01-28T05:51:20.007+00:00

They are sorted incorrectly in the rectangles.

Actually,in your query ,they are sorted right.
Please check this document ,as it says
Therefore the ORDER BY clause in OVER( ... ) only defines the logical order of the rows within each partition of the result set.

Wheat, Samuel G (Contractor) 21 Reputation points

2022-01-28T16:42:49.18+00:00

Therefore the ORDER BY clause in OVER( ... ) only defines the logical order of the rows within each partition of the result set.

OK this sounds right. The question remains however - Why is computation not restarting for each partition?
In the screen cap below I have identified the start of each partition in yellow. These are rows where the value in the Response column changes when the rows are sorted by SerialNumber, InterrogationDate.

The expectation is that the value in the SameConsecutiveResponseCount column will be reset to zero for each row where Response is yellow.

It is further expected that the value in SameConsecutiveResponseCount will be incremented by one for each row where the Response column does not change.

When the value in the Response column changes again, it is expected that computation restarts i.e. SUM is set to zero.

The first two partitions in the image below are correct.

Erland Sommarskog 101.7K Reputation points MVP

2022-01-28T22:01:37.357+00:00

OK this sounds right. The question remains however - Why is computation not restarting for each partition?

Because just as LiHong has explained to you: you only have tw partitions.

sum(case when Response = LastResponse then 1 else 0 end) over(partition by Response order by InterrogationDate)

There are only two possible values for Response, 0 and 1. And that is what PARTITION BY means. in an OVER clause PARTITION BY happens first and ORDER BY is applied once the partition has been completed; not the other way round.

There is no way to express in T-SQL what is going on here. In ANSI SQL there is something called Row Pattern Recognition would fit in very well here, but it has yet to appear in SQL Server. (I believe it is only implemented by Oracle and one more product.)

Wheat, Samuel G (Contractor) 21 Reputation points

2022-01-28T23:22:09.257+00:00

Thanks @Erland Sommarskog I did not understand what @LiHong-MSFT was trying to say.
Now that you explain it, it seems severely crippled - I am not grouping data here.
In any case, thanks for your explanation. This might be a good topic for one of your excellent presentations.

Erland Sommarskog 101.7K Reputation points MVP

2022-01-29T10:18:09.923+00:00

I think LiHong did a very good job of explaining the issue. The point where Li failed was to identify you hiccup, and I can certainly not blame Li for that, since it took me quite a while to see where your error of thinking was.

As for being crippled, the feature works as designed. Keep in mind that computers are very stupid. They never read between the lines and say "Ah, so you mean this" and change the rules.
Sign in to comment

How to count consecutive values across rows?

5 additional answers