MSSQL Create and Conditionally Increment a Column

Question

I am getting some data (into a #temp table) that has some logical ordering.

When I'm pulling the data I'd like to add a new sequence/counter that increments only when certain conditions are met within the other fields.

Ideally something like this:

DECLARE @counter int = 0;

SELECT Item, Date, Event, 
@counter = @counter + 
    (CASE 
        WHEN Event = 'Something' THEN 1 
        ELSE 0 
    END) AS EVENT_SEQ
INTO #tempTable
FROM  MyData
ORDER BY Item, Date

SQL gives me two errors:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

or

A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

The desired result

ITEM    DATE        EVENT       EVENT_SEQ
blah    2015-01-01  nothing     1
blah    2015-01-02  nothing     1
blah    2015-01-03  nothing     1
blah    2015-01-04  something   2
blah    2015-01-05  nothing     2
blah    2015-01-06  nothing     2
blah    2015-01-07  something   3
blah    2015-01-08  nothing     3
blah    2015-01-09  nothing     3
blah    2015-01-10  nothing     3
blah    2015-01-11  nothing     3
blah    2015-01-12  something   4
blah    2015-01-13  something   5
blah    2015-01-14  nothing     5
blah    2015-01-15  nothing     5
blah    2015-01-16  nothing     5
blah    2015-01-17  nothing     5
blah    2015-01-18  nothing     5
blah    2015-01-19  nothing     5
blah    2015-01-20  something   6
blah    2015-01-21  something   7
blah    2015-01-22  nothing     7

Similar question:

SQL Server 2012: Conditionally Incrementing a counter user ROW_NUMBER()

I am using SQL 2014

score 5 · Accepted Answer · answered Jun 10 '15 at 06:03

This seems to produce your desired results. When trying to get proficient at writing SQL, it's most beneficial to try to think in terms of the entire set of results, rather than thinking about "line by line" processing.

As such, I've re-cast your spec as:

EVENT_SEQ is the number of something events that occur before or on the current row, +1

declare @t table (ITEM char(4), [Date] date, [Event] varchar(9))
insert into @T(ITEM,[DATE],[EVENT]) values
('blah','20150101','nothing'),
('blah','20150102','nothing'),
('blah','20150103','nothing'),
('blah','20150104','something'),
('blah','20150105','nothing'),
('blah','20150106','nothing'),
('blah','20150107','something'),
('blah','20150108','nothing'),
('blah','20150109','nothing'),
('blah','20150110','nothing'),
('blah','20150111','nothing'),
('blah','20150112','something'),
('blah','20150113','something'),
('blah','20150114','nothing'),
('blah','20150115','nothing'),
('blah','20150116','nothing'),
('blah','20150117','nothing'),
('blah','20150118','nothing'),
('blah','20150119','nothing'),
('blah','20150120','something'),
('blah','20150121','something'),
('blah','20150122','nothing')

select *,
  SUM(CASE WHEN [event]='something' THEN 1 ELSE 0 END) OVER
  (ORDER BY item,date
   ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW)+1 as EVENT_SEQ
from @t
ORDER BY item,date

Where you can hopefully see that I've translated it almost directly into a windowed aggregate function (SUM() OVER()). Results:

ITEM Date       Event     EVENT_SEQ
---- ---------- --------- -----------
blah 2015-01-01 nothing   1
blah 2015-01-02 nothing   1
blah 2015-01-03 nothing   1
blah 2015-01-04 something 2
blah 2015-01-05 nothing   2
blah 2015-01-06 nothing   2
blah 2015-01-07 something 3
blah 2015-01-08 nothing   3
blah 2015-01-09 nothing   3
blah 2015-01-10 nothing   3
blah 2015-01-11 nothing   3
blah 2015-01-12 something 4
blah 2015-01-13 something 5
blah 2015-01-14 nothing   5
blah 2015-01-15 nothing   5
blah 2015-01-16 nothing   5
blah 2015-01-17 nothing   5
blah 2015-01-18 nothing   5
blah 2015-01-19 nothing   5
blah 2015-01-20 something 6
blah 2015-01-21 something 7
blah 2015-01-22 nothing   7

Both this and @arun's answer worked great for me. I ran some tests to try to see if there was an optimization of either one but analyzer didn't throw me any red flags for either way of doing it. +1 for introducing me to the `ROWS BETWEEN UNBOUNDED PRECEDING` concepts. Definitely will explore all this more. — J Webb, Jun 12 '15 at 02:52

score 1 · Answer 2 · answered Jun 10 '15 at 07:06

1

you can use this query to Insert data in temp table

select Item,[Date] ,[Event],
  SUM(CASE WHEN event = 'something' THEN 1 ELSE 0 END)  OVER  (ORDER BY Item,date  )+1 as sequence INTO #tempTable
from MyData
ORDER BY item,date

answered Jun 10 '15 at 07:06

Arun Gairola

884
4
14

MSSQL Create and Conditionally Increment a Column

2 Answers2