CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
hurlski
hurlski

Reputation: 157

SQL Server 2012 Assign an ID based on the occurrence of null values

I have the following table:

ID  Stop
---------
1   NULL
2   100
3   NULL
4   50
5   100
6   210
7   300
8   NULL
9   NULL
10  20
11  70

What I am trying to do is to create a new column that assigns an ID based on the location of the NULL values. Basically, the Stop column is the amount of time that has been spent stopped and the NULL value is time spent in motion. I would like consecutive stops (the id column is the chronological order) to be able to be identified with some sort of identifier. This is what I would like:

ID  Stop    StopID
------------------
1   NULL    NULL
2   100     1
3   NULL    NULL
4   50      2
5   100     2
6   210     2
7   300     2
8   NULL    NULL
9   NULL    NULL
10  20      3
11  70      3

I have been tinkering with this query, but I can't seem to get it right:

select a.id, a.Stop,
       (case when a.Stop IS NOT NULL
             then sum(case when a.Stop IS NOT NULL then 1 end) over (partition by x order by id)
        end) as StopID
from (select a.*,
             sum(case when a.Stop IS NOT NULL then 0 else 1 end ) over (order by id) as x
      from data a
     ) a

it yields the following:

ID  Stop    StopID
-------------------
1   NULL    NULL
2   100     1
3   NULL    NULL
4   50      1
5   100     2
6   210     3
7   300     4
8   NULL    NULL
9   NULL    NULL
10  20      1
11  70      2

Upvotes: 1

Views: 69

Answers (1)

dean
dean

Reputation: 10098

create table #t (ID int, Stop int)

insert #t values
(1,   NULL),
(2,   100),
(3,   NULL),
(4,   50),
(5,   100),
(6,   210),
(7,   300),
(8,   NULL),
(9,   NULL),
(10,  20),
(11,  70)

select id, stop,
case when stop is null then null else sum(case when stop is null then 1 else 0 end) over(order by id) end as stop_id
from #t

Upvotes: 1

Related Questions