CODEWITHSUNDEEP
sqlsql-servert-sql
nmess88
nmess88

Reputation: 420

SQL Server : set a row value based on a condition

I don't know what would be the appropriate title for this problem, but here is what I need to accomplish

Here is my dataset:

State          TimeInState
--------------------------
  1                 20
  3                  0
  4                  5
  8                  2
  5                  10
  1                  18
  3                  30 
  12                 2 
  2                  0

What I want is another column in here, lets say FooID. What FooID is a int value that will remain same until the state is 1 again.

So the dataset would look like this:

State          TimeInState          FooID
------------------------------------------
  1                 20                1
  3                  0                1
  4                  5                1
  8                  2                1
  5                  10               1
  1                  18               2
  3                  30               2
  12                 2                2
  2                  0                2

So if there was another row at the end with State=1 then FooID will be 3 until the next state is changed.

How can I accomplish this in T-SQL?

Thanks in advance.

Upvotes: 1

Views: 1289

Answers (1)

ZLK
ZLK

Reputation: 2874

If you have some way of ordering rows (like an ID of sorts), then here is an example of how you could do something like this:

DECLARE @T TABLE (ID INT IDENTITY(1, 1), State INT, TimeInState INT)
INSERT @T (State, TimeInState) 
VALUES (1, 20), (3, 0), (4, 5), (8, 2), (5, 10), (1, 18)
     , (3, 30), (12, 2), (2, 0), (1, 1), (1, 1), (2, 1);

WITH CTE AS (
    SELECT *
         , ROW_NUMBER() OVER (ORDER BY CASE WHEN State = 1 THEN 0 ELSE 1 END, ID) RN
    FROM @T
    )
SELECT State, TimeInState, Foo.FooID
FROM CTE T
CROSS APPLY (SELECT MAX(RN) FooID FROM CTE WHERE State = 1 AND ID <= T.ID) Foo
ORDER BY ID;

But if you don't have the data ordered in some way already, then I don't think you can ensure the result set will sort the data in the way you want to sort it.

Upvotes: 2

Related Questions