Loop that changes contents

Question

I'm really struggling with something that should be straight forward, I have a table in the following format:

ID  Date    Agent   State
1   02/01/2015  77777   Work
2   03/01/2015  77777   X
3   04/01/2015  77777   X
4   05/01/2015  77777   X
5   06/01/2015  77777   X
6   07/01/2015  77777   SICK
7   08/01/2015  77777   SICK
8   09/01/2015  77777   X
9   02/01/2015  88888   Work
10  03/01/2015  88888   Work
11  04/01/2015  88888   SICK
12  05/01/2015  88888   SICK
13  06/01/2015  88888   X
14  07/01/2015  88888   X
15  08/01/2015  88888   SICK
16  09/01/2015  88888   Work

I need to create a loop that replaces the X's with the state of the preceding record until it reaches a field that doesn't contain X. It would also have to cater for a change in Agent ID and also if the first record for an agent is X then it should show "Work"

The expected output would look like:

Date    Agent   State
02/01/2015  77777   Work
03/01/2015  77777   Work
04/01/2015  77777   Work
05/01/2015  77777   Work
06/01/2015  77777   Work
07/01/2015  77777   SICK
08/01/2015  77777   SICK
09/01/2015  77777   SICK
02/01/2015  88888   Work
03/01/2015  88888   Work
04/01/2015  88888   SICK
05/01/2015  88888   SICK
06/01/2015  88888   SICK
07/01/2015  88888   SICK
08/01/2015  88888   SICK
09/01/2015  88888   Work

I'm using sql server management studio 2008

please elaborate this part "It would also have to cater for a change in Agent ID and also if the first record for an agent is X then it should show "Work" — Sateesh Pagolu, Sep 16 '15 at 10:03
Hi, The data I have has either 3 states ("X" = rest day or holiday, Sick or Work) if an agents first state = "X" then I need this to be changed to "Work" so the loop would fill in the records below until it reached another state of "Work" or a state of "Sick". The change in agent id should reset the loop so it doesn't carry down a state from the preceding agent. I hope that makes sense. — Tino11, Sep 16 '15 at 10:05

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

You can use a sub query inside an update statement, with a COALESCE to assign 'Work' if the row with ID 1 is 'X' like this.

update #statetable 
set [State] = COALESCE((select top 1 s2.[State] from #statetable as s2 where s2.ID < s1.ID AND s2.State <> 'X' order by s2.ID ),'Work')
from #statetable as s1
Where s1.[State] = 'X'

Here is a sqlfiddle of the code I used to test it.

Note:

If you declare your tables State column to be type varchar with no length an error will be thrown.

String or binary data would be truncated.

Because a varchar without a length will default to 1. So table defined like this will not work.

CREATE TABLE #statetable ( [ID] int, [State] varchar );

edited May 23 '17 at 10:27

Community

1
1

answered Sep 16 '15 at 10:20

Padhraic

5,112
4
30
39

It looks to work fine with just the ID and State but when I try to introduce the date and agent id fields it doesn't quite work. It seems to change the X's to Work even if the preceding record had a state of Sick. – Tino11 Sep 16 '15 at 10:50
I have included date and agent fields in my sample, it worked and the results matched your expected results. Perhaps there are some other parts to our SQL script that is causing the error. – Padhraic Sep 16 '15 at 11:00

score 1 · Answer 2 · answered Sep 16 '15 at 11:05

The most straightforward approach is to use a cursor.

DECLARE @id int
DECLARE @agent int
DECLARE @state varchar(10)
DECLARE @previousAgent int = 0
DECLARE @previousState varchar(10) = 'Work'

DECLARE myCursor CURSOR FOR
    SELECT ID, Agent, State
    FROM MyTable
    ORDER BY Agent, Date

OPEN myCursor   
FETCH NEXT FROM myCursor INTO @id, @agent, @state

WHILE @@FETCH_STATUS = 0   
BEGIN
    IF @agent <> @previousAgent
    BEGIN
        SET @previousAgent = @agent
        SET @previousState = 'Work'
    END

    IF @state = 'X'
        UPDATE MyTable SET State = @previousState WHERE ID = @id
    ELSE
        SET @previousState = @state

    FETCH NEXT FROM myCursor INTO @id, @agent, @state
END

CLOSE myCursor
DEALLOCATE myCursor

Fiddle: http://sqlfiddle.com/#!3/20716/1/0

Loop that changes contents

2 Answers2