CODEWITHSUNDEEP
sqlsql-serversql-server-2008database-partitioning
rickyProgrammer
rickyProgrammer

Reputation: 1167

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

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.

enter image description here

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.

enter image description here

Upvotes: 0

Views: 4903

Answers (5)

Franckess
Franckess

Reputation: 41

In case there is an extra element in the tenant column

create table #tenant (tenant varchar(20), area int, date date, sales int)
insert into #tenant values
('little asia', 18, '20150101', 10),
('little asia', 18, '20150102', 20),
('little asia', 20, '20150103', 30),
('little asia', 20, '20150104', 10),
('little asia', 18, '20150105', 20),
('little asia', 18, '20150106', 30),
('little', 18, '20150101', 10),
('little', 18, '20150102', 20),
('little', 18, '20150103', 30),
('little', 18, '20150104', 10),
('little', 18, '20150105', 20),
('little', 11, '20150106', 30);

The code will be written as follows:

/* new code adding tenant*/
SELECT tenant, area, [date], sales,
       DENSE_RANK() OVER (PARTITION BY tenant ORDER BY tenant, grpOrder) AS counter
FROM (
  SELECT tenant, area, date, sales,       
         MIN([date]) OVER (PARTITION BY tenant, area, grp) AS grpOrder
FROM (
  SELECT tenant, area, [date], sales,           
        ROW_NUMBER() OVER (PARTITION BY tenant ORDER BY tenant, date) -
       ROW_NUMBER() OVER (PARTITION BY tenant, area ORDER BY tenant, [date]) AS grp
FROM #tenant ) AS t ) AS s
order by tenant, date

Upvotes: 1

treenode
treenode

Reputation: 21

The accepted answer works well and the SQL Fiddle demo is great. However, it doesn't take into account the situation with multiple tenants.

I extended the SQL Fiddle answer and the link is here for those people whose datasets comprise multiple tenants, simply by ensuring that tenant was present in each PARTITION BY and ORDER BY.

Upvotes: -1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 2

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

You can do it like this with window functions:

declare @data table(name varchar(10), area int, dates datetime, sales int)
insert into @data(name, area, dates, sales) values
('Little Asia', 18, '20150101', 10)
, ('Little Asia', 18, '20150102', 20)
, ('Little Asia', 20, '20150103', 30)
, ('Little Asia', 20, '20150104', 10)
, ('Little Asia', 18, '20150105', 20)
, ('Little Asia', 18, '20150106', 30)

Select name, area, dates, sales
    , [counter] = DENSE_RANK() over(order by c)
    , [count] = Count(*) over(partition by n ,c)
From (
    Select name, area, dates, sales, n 
        , c = ROW_NUMBER() over(order by n, dates) - ROW_NUMBER() over(partition by area, n order by dates)
    From (
        Select name, area, dates, sales 
            , n = ROW_NUMBER() over(order by dates) - ROW_NUMBER() over(partition by area order by dates)
        From @data
    ) as x
) as v
order by dates

Output:

name        area    dates       sales   counter count
Little Asia 18      2015-01-01  10      1       2
Little Asia 18      2015-01-02  20      1       2
Little Asia 20      2015-01-03  30      2       2
Little Asia 20      2015-01-04  10      2       2
Little Asia 18      2015-01-05  20      3       2
Little Asia 18      2015-01-06  30      3       2

Upvotes: 1

Brad D
Brad D

Reputation: 762

As long as there is a difference of atleast 1 (@threshold) we you will start a new group. this will partition by tenant.

DECLARE @Table as TABLE (
Tenant varchar(20),
Area int,
[date] Date,
Sales int
)
INSERT INTO @Table
VALUES
('Little Asia',18,'1/1/2015', 10),
('Little Asia',18,'1/2/2015', 20),
('Little Asia',20,'1/3/2015', 30),
('Little Asia',20,'1/4/2015', 10),
('Little Asia',18,'1/5/2015', 20),
('Little Asia',18,'1/6/2015', 30)

/***** Begin Query *****/
DECLARE @Threshold INT = 1
;WITH C1 AS
(
SELECT Tenant, Area, [Date], Sales,
CASE WHEN ABS(Area - LAG(Area) OVER(PARTITION BY Tenant ORDER BY [Date])) <= @Threshold THEN NULL ELSE 1 END AS isstart
FROM @Table
 ),
C2 AS
 (
SELECT Tenant, Area, [Date], Sales, COUNT(isstart) OVER( PARTITION BY Tenant ORDER BY [Date] ROWS UNBOUNDED PRECEDING) AS grp
FROM C1
 )
SELECT * FROM C2

Upvotes: 0

Related Questions