none
Update Table base on value in previous record RRS feed

  • Question

  • I believe the title over simplifies the question but maybe not. I need to update a table by selecting the pervious record value. As you can see below I have to tables Shift_Log and Shift_Printers. When the select below is ran the following is the results. On some of the records the user has entered their own Initials into the SP_From field. This should be the previous Operators Initials. I need to run a update query to fix this issue. So record 1283 should have 'RB' in the SP_From field not 'TN'. I tried a few queries but nothing achieved the desired results.

    SELECT SL_Shift_Key
         , SL_Operator
         , SP_Printer_Id
         , SP_From
         , SP_LogIn_Date
    FROM Shift_Log INNER JOIN Shift_Printers 
    ON SL_Shift_Key = SP_Shift_Key
    WHERE (((SP_Printer_Id)='VP1'))
    ORDER BY SL_LogIn_Date;

    SL_Shift_Key

    SL_Operator

    SP_Printer_Id

    SP_From

    SP_LogIn_Date

    1279

    LJ

    VP1

    NULL

    10/30/14   6:00 AM

    1281

    RB

    VP1

    LJ

    10/30/14   6:00 PM

    1283

    TN

    VP1

    TN

    10/31/14   2:00 AM

    1286

    NW

    VP1

    TN

    10/31/14   6:00 PM

    1288

    DG

    VP1

    DG

    11/1/14   6:00 AM

    1289

    KW

    VP1

    KW

    11/1/14   5:59 PM

    1293

    MK

    VP1

    KW

    11/2/14   6:00 PM

    1295

    ???

    VP1

    MK

    11/3/14   6:00 AM

    1296

    LM

    VP1

    LM

    11/3/14   6:00 PM

    1302

    RB

    VP1

    VP

    11/4/14   6:00 PM

    1305

    TN

    VP1

    TN

    11/5/14   2:00 AM

    1308

    KW

    VP1

    KW

    11/5/14   5:59 PM

    1310

    DG

    VP1

    DG

    11/6/14   6:00 AM

    1314

    NW

    VP1

    DG

    11/6/14   6:00 PM

    1317

    LJ

    VP1

    LJ

    11/7/14   6:00 AM

    1319

    RB

    VP1

    LJ

    11/7/14   6:00 PM

    1321

    TN

    VP1

    TN

    11/8/14   2:00 AM

    1325

    NW

    VP1

    TN

    11/8/14   6:00 PM

    1327

    ???

    VP1

    NW

    11/9/14   6:00 AM

    1328

    LM

    VP1

    LM

    11/9/14   9:05 PM

    1330

    VP

    VP1

    LM

    11/10/14   1:10 PM


    Thursday, January 9, 2020 3:44 PM

Answers

  • ;with mycte as (
    SELECT SL_Shift_Key
         , SL_Operator
         , SP_Printer_Id
         , SP_From
         , SP_LogIn_Date
    	 ,row_number()Over(Order by SL_Shift_Key) rn
    FROM Shift_Log INNER JOIN Shift_Printers 
    ON SL_Shift_Key = SP_Shift_Key
    WHERE (((SP_Printer_Id)='VP1'))
    
    )
    ,mycte2 as (
    
    select m1.*,m2.SL_Operator leadSL_Operator 
    from mycte m1 
    left join mycte m2 on m1.rn=m2.rn+1
    )
    Merge Shift_Printers tgt
    using mycte2 src   ON src.SL_Shift_Key = tgt.SP_Shift_Key
    When matched then 
    Update
    Set SP_From=src.leadSL_Operator;

    Thursday, January 9, 2020 6:17 PM
    Moderator

All replies

  • ;with mycte as (
    SELECT SL_Shift_Key
         , SL_Operator
         , SP_Printer_Id
         , SP_From
         , SP_LogIn_Date
    	 ,lag(SL_Operator)Over(Order by SL_Shift_Key) leadSL_Operator
    FROM Shift_Log INNER JOIN Shift_Printers 
    ON SL_Shift_Key = SP_Shift_Key
    WHERE (((SP_Printer_Id)='VP1'))
    
    )
    Merge Shift_Printers tgt
    using mycte src   ON src.SL_Shift_Key = tgt.SP_Shift_Key
    When matched then 
    Update
    Set SP_From=src.leadSL_Operator;

    Thursday, January 9, 2020 4:06 PM
    Moderator
  • Good answer but as this is a legacy 2008R server Lag is not available. But I am thinking using a CTE could get me close. Thanks sorry I didn't mention what server I was on.
    Thursday, January 9, 2020 5:11 PM
  • Your sample data do not match the results from the query you posted. Can you explain a bit more about the issue?

    A Fan of SSIS, SSRS and SSAS

    Thursday, January 9, 2020 5:42 PM
  • ;with mycte as (
    SELECT SL_Shift_Key
         , SL_Operator
         , SP_Printer_Id
         , SP_From
         , SP_LogIn_Date
    	 ,row_number()Over(Order by SL_Shift_Key) rn
    FROM Shift_Log INNER JOIN Shift_Printers 
    ON SL_Shift_Key = SP_Shift_Key
    WHERE (((SP_Printer_Id)='VP1'))
    
    )
    ,mycte2 as (
    
    select m1.*,m2.SL_Operator leadSL_Operator 
    from mycte m1 
    left join mycte m2 on m1.rn=m2.rn+1
    )
    Merge Shift_Printers tgt
    using mycte2 src   ON src.SL_Shift_Key = tgt.SP_Shift_Key
    When matched then 
    Update
    Set SP_From=src.leadSL_Operator;

    Thursday, January 9, 2020 6:17 PM
    Moderator