SQL Server Query to Count Number of Changing Values in a Column Sequentially

Question

I need to count the number of changing values in a column sequentially. Please see image for illustration (correct or expected output)

In here, the column Area is changing, counter column should display the sequential counter based on the changing values in area.

I have started with this code

    SELECT a.tenant, a.area, a.date , a.gsc, f.counter
    FROM TENANT a
        inner join
            (SELECT a.tenant, COUNT(DISTINCT e.Area) AS counter
             FROM TENANT
             GROUP BY tenant
            ) AS f ON a.tenant = f.tenant
   order by a.tenant, a.date

And gives me this output. Counting the number of distinct values found in Area column IN ALL rows.

Answer 1

Here's one way to do it using window functions:

SELECT tenant, area, [date], sales,
       DENSE_RANK() OVER (ORDER BY grpOrder) AS counter
FROM (
  SELECT tenant, area, date, sales,       
         MIN([date]) OVER (PARTITION BY area, grp) AS grpOrder
  FROM (
    SELECT tenant, area, [date], sales,           
           ROW_NUMBER() OVER (ORDER BY date) -
           ROW_NUMBER() OVER (PARTITION BY area ORDER BY [date]) AS grp
    FROM tenant ) AS t ) AS s

The inner query identifies islands of consecutive area values. See grp value in below partial output from this sub-query:

area date       grp
--------------------
18   2015-01-01  0
18   2015-01-02  0
18   2015-01-05  2
18   2015-01-06  2
20   2015-01-03  2
20   2015-01-04  2

Using window version of MIN we can calculate grp order: field grpOrder holds the minimum date per group.

Using DENSE_RANK() in the outer query we can now easily calculate counter values: first group gets a value of 1, next group a value of 2, etc.

Demo here