Set value based on previous row

  • I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.

    Anyway the code is....

    declare @data table

    (ID int identity(1,1),

    DeviceID int,

    EventTypeID int,

    EventID int)

    insert into @data (DeviceID, EventTypeID)

    values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),

    (200, 0),(200, 4),(200, 2),(200, 3),(200, 3)

    update t1

    set eventid = case when t2.EventTypeID IN (1,2,4) and t1.EventTypeID in (0, 3) then ISNULL(t2.EventID + 1, 1)

    when t2.EventTypeID IN (0, 3) and t1.EventTypeID in (1, 2, 4) then ISNULL(t2.EventID + 1, 1)

    else ISNULL(t2.EventID + 1, 1) end

    from @data t1

    inner join @data t2 on t1.ID = t2.ID + 1

    select * from @data

    [/code]

    So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.

    Next the rule is coded in the case statement above.

    So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.

  • Can you also add the desired output to make it easier to understand what you actually mean to convey with this..

    See if ranking functions can help you with sequencing numbers. http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/[/url]

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • Check this article: http://www.sqlservercentral.com/articles/T-SQL/62159/

    --Vadim R.

  • Ok thanks for the replies so far. I checked out that link and it makes sense but I cannot make something like that work for my particular problem.

    Here is the required output. It would be much easier if the EventID could be dependent on DeviceID but the customer has asked if it could be independent of DeviceID.

    ID DeviceID EventTypeID EventID

    1 100 1 1

    2 100 2 1

    3 100 4 1

    4 100 0 2

    5 100 3 2

    6 200 0 3

    7 200 4 4

    8 200 2 4

    9 200 3 5

    10 200 3 5

  • The 1st problem I have is I need to track the previous EventID. Here is what I have so far when partitioning on DeviceID. Looking at DeviceID 100 then row 4 should remain at value 2.

    declare @data table

    (ID int identity(1,1),

    DeviceID int,

    EventTypeID int,

    EventID int)

    insert into @data (DeviceID, EventTypeID)

    values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),

    (200, 0),(200, 4),(200, 2),(200, 3),(200, 3);

    UPDATE @data

    SET EventID = 1

    select *,

    case when currrow.EventTypeID IN (1,2,4) and nextrow.EventTypeID in (0, 3) then ISNULL(prevrow.EventID + 1, 1)

    when currrow.EventTypeID IN (0, 3) and nextrow.EventTypeID in (1, 2, 4) then ISNULL(prevrow.EventID + 1, 1)

    else ISNULL(nextrow.EventID, 1) end

    from @data currrow

    left join @data nextrow on currrow.ID = nextrow.ID -1

    left join @data prevrow on currrow.ID = prevrow.ID +1

    select * from @data

  • Ok this is the draft query which works using DeviceID to base the EventID on. I have had to use a where loop in order to set the value so that the next iteration can use this to increment if (if this is neccessary based on the rules)

    Anyone have any other take comments?

    declare @data table

    (ID int identity(1,1),

    DeviceID int,

    EventTypeID int,

    EventID INT,

    ImportGroup int)

    insert into @data (DeviceID, EventTypeID)

    values (100, 1),(100, 2),(100, 4),(100, 0),(100, 3),

    (200, 0),(200, 4),(200, 2),(200, 3),(200, 3);

    ;WITH cte AS (

    SELECT ID, DeviceID, EventTypeID, EventID, ImportGroup,

    ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY id) AS rownum

    FROM @data)

    update cte

    set ImportGroup = rownum

    UPDATE currow

    SET eventid = ISNULL(prevrow.EventID + 1, 1)

    from @data currow

    left join @data nextrow on currow.ID = nextrow.ID -1

    left join @data prevrow on currow.ID = prevrow.ID +1

    DECLARE @ImportGroup INT

    SET @ImportGroup = 1

    WHILE (SELECT COUNT(*) FROM @data WHERE ImportGroup=@ImportGroup)>0

    BEGIN

    UPDATE currow

    SET eventid = case when prevrow.EventTypeID IN (1,2,4) and currow.EventTypeID in (0, 3) then ISNULL(prevrow.EventID + 1, 1)

    when prevrow.EventTypeID IN (0, 3) and currow.EventTypeID in (1, 2, 4) then ISNULL(prevrow.EventID + 1, 1)

    else ISNULL(prevrow.EventID, 1) end

    from @data currow

    left join @data nextrow on currow.deviceid = nextrow.deviceid AND currow.ID = nextrow.ID -1

    left join @data prevrow on currow.deviceid = prevrow.deviceid AND currow.ID = prevrow.ID +1

    SET @ImportGroup = @ImportGroup + 1

    END

    select * from @data

  • ;WITH a (ID,ImportGroup) AS (

    SELECTc.ID,ROW_NUMBER() OVER (ORDER BY c.ID)

    FROM@data c

    LEFT JOIN @data n ON n.ID = c.ID + 1

    WHERE(c.EventTypeID IN (1,2,4) AND n.EventTypeID in (0, 3))

    OR(c.EventTypeID IN (0, 3) and n.EventTypeID in (1, 2, 4))

    ORc.DeviceID <> n.DeviceID

    ORn.DeviceID IS NULL

    ),

    b (FromID,ToID,ImportGroup) AS (

    SELECTISNULL(a1.ID+1,1),a.ID,a.ImportGroup

    FROMa

    LEFT JOIN a a1 On a1.ImportGroup = a.ImportGroup - 1

    )

    SELECT d.ID,d.DeviceID,d.EventTypeID,b.ImportGroup

    FROM @data d

    JOIN b ON d.ID BETWEEN b.FromID AND b.ToID

    Performance could be an issue here due to table scans.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply