CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
user2910240
user2910240

Reputation: 171

SQL Increment number in select statement

I have an issue where I need group a set of values and increase the group number when the variance between 2 columns is greater than or equal to 4, please see below.

UPDATE: I added a date column so you can view the order, but I need the group to update based off of the variance not the date.

+--------+-------+-------+----------+--------------+ | Date | Col 1 | Col 2 | Variance | Group Number | +--------+-------+-------+----------+--------------+ | 1-Jun | 2 | 1 | 1 | 1 | | 2-Jun | 1 | 1 | 0 | 1 | | 3-Jun | 3 | 2 | 1 | 1 | | 4-Jun | 4 | 1 | 3 | 1 | | 5-Jun | 5 | 1 | 4 | 2 | | 6-Jun | 1 | 1 | 0 | 2 | | 7-Jun | 23 | 12 | 11 | 3 | | 8-Jun | 12 | 11 | 1 | 3 | | 9-Jun | 2 | 1 | 1 | 3 | | 10-Jun | 13 | 4 | 9 | 4 | | 11-Jun | 2 | 1 | 1 | 4 | +--------+-------+-------+----------+--------------+

Upvotes: 0

Views: 2796

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The group number is simply the number of times that 4 or greater appears in the variance column. You can get this using a correlated subquery:

select t.*,
       (select 1 + count(*)
        from table t2
        where t2.date < t.date and t2.variance >= 4
       ) as GroupNumber
from table t;

In SQL Server 2012+, you can also do this using a cumulative sum:

select t.*,
       sum(case when variance >= 4 then 1 else 0 end) over
            (order by date rows between unbounded preceding and 1 preceding
            ) as GroupNumber
from table t;

Upvotes: 2

Related Questions