CODEWITHSUNDEEP
sql-servert-sql
J Webb
J Webb

Reputation: 711

MSSQL Create and Conditionally Increment a Column

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

Upvotes: 2

Views: 1305

Answers (2)

Arun Gairola
Arun Gairola

Reputation: 884

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 5

Related Questions