Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2017
    Posts
    9

    Answered: Need SQL to previous row values from current row's values

    Hi, Need sql to update the status value of first instance/occurrence of a row which got status changed, from it's just previous row's status value. The load_date should be in sort order ASC. The sql should update the entire table for all tr-ids.

    In below examples shows which rows should be updated and which not.

    example 1
    HTML Code:
    TR_ID	LOAD_DATE	     Status	update expected
    10001	2014-07-19-17.51.00	 O	Don't update
    10001	2014-09-19-20.02.00	 T	T -> O
    10001	2015-02-02-20.02.00	 T	Don't update
    10001	2015-02-03-20.02.00	 T	Don't update
    10001	2015-02-04-20.02.00	 T	Don't update
    10001	2015-02-05-20.03.00	 T	Don't update
    10001	2015-02-06-20.03.00	 T	Don't update
    10001	2015-02-09-20.03.00	 C	C -> T
    10001	2015-02-10-20.02.00	 C	Don't update

    example 2.
    HTML Code:
    TR_ID	LOAD_DATE	    Status	update expected
    10002	2014-07-19-17.51.00	 O	Don't update
    10002	2014-09-19-20.02.00	 O	Don't update
    10002	2015-02-02-20.02.00	 T	T -> O
    10002	2015-02-03-20.02.00	 T	Don't update
    10002	2015-02-04-20.02.00	 T	Don't update
    10002	2015-02-05-20.03.00	 T	Don't update
    10002	2015-02-06-20.03.00	 C	C -> T
    10002	2015-02-09-20.03.00	 C	Don't update
    10002	2015-02-10-20.02.00	 C	Don't update

    Thank you in advance.
    Srini
    Last edited by srinivas_sp; 09-13-17 at 10:05.

  2. Best Answer
    Posted by mark.bb

    "Hi,

    Try this:
    Code:
    update mytable
    set status=coalesce(nullif(lag(status, 1) over (partition by tr_id order by load_date), status), status);
    "


  3. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    577
    Provided Answers: 3
    Although your requirements are not clear for me, here is an algorithm you should follow:
    - get MAX(LOAD_DATE) for the same TR_ID where LOAD_DATE < LOAD_DATE_of_the_current_row
    - get the Status for that max_load_date and check if it is different from the Status of the current row
    - if yes, update the Status
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    Hi,

    Try this:
    Code:
    update mytable
    set status=coalesce(nullif(lag(status, 1) over (partition by tr_id order by load_date), status), status);
    Regards,
    Mark.

  5. #4
    Join Date
    Aug 2017
    Posts
    9
    Mark, Iasi thanks for your replies, but unfortunately they won't work for me as the requirement has slightly changed. I have updated the post with new requirement, and provided examples of which rows should be updated and which shouldn't be.

    Appreciate any help with a sql

  6. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    If you even don't want to try it, I'll do it for you.
    Run the following statements as is:

    Code:
    select t.*, coalesce(nullif(lag(status, 1) over (partition by tr_id order by load_date), status), status) status_new
    from table(values
      (10001, '2014-07-19-17.51.00', 'O')
    , (10001, '2014-09-19-20.02.00', 'T')
    , (10001, '2015-02-02-20.02.00', 'T')
    , (10001, '2015-02-03-20.02.00', 'T')
    , (10001, '2015-02-04-20.02.00', 'T')
    , (10001, '2015-02-05-20.03.00', 'T')
    , (10001, '2015-02-06-20.03.00', 'T')
    , (10001, '2015-02-09-20.03.00', 'C')
    , (10001, '2015-02-10-20.02.00', 'C')
    ) t(TR_ID, LOAD_DATE, Status)
    order by tr_id, load_date;
    
    select t.*, coalesce(nullif(lag(status, 1) over (partition by tr_id order by load_date), status), status) status_new
    from table(values
      (10002, '2014-07-19-17.51.00', 'O')
    , (10002, '2014-09-19-20.02.00', 'O')
    , (10002, '2015-02-02-20.02.00', 'T')
    , (10002, '2015-02-03-20.02.00', 'T')
    , (10002, '2015-02-04-20.02.00', 'T')
    , (10002, '2015-02-05-20.03.00', 'T')
    , (10002, '2015-02-06-20.03.00', 'C')
    , (10002, '2015-02-09-20.03.00', 'C')
    , (10002, '2015-02-10-20.02.00', 'C')
    ) t(TR_ID, LOAD_DATE, Status)
    order by tr_id, load_date;
    The last column shows you the new STATUS value, which you will have if you run the statement from my initial reply.
    How does this output differ from your expectation?
    Regards,
    Mark.

  7. #6
    Join Date
    Aug 2017
    Posts
    9
    Hi Mark, Looks like your solution is working for me when I tried on one tr_id and now I would like to extend the same solution to entire table on every tr_id, note that each tr_id contains similar bunches records and they need same update.

    appreciate if you can extend me the solution to the entire table by tr_id. I have updated my original post providing data for two tr_ids.

    appreciate your help and thank you so much for the solution you have provided.

  8. #7
    Join Date
    Aug 2017
    Posts
    9
    You're correct Mark. It's working but I need a generic sql that should update on each tr_id through the entire table without having to provide tr_id in the sql, because it's a huge table.

    I tried below sql and it works as expected.
    SELECT tr_id, status, load_date, coalesce(nullif(lag(status, 1) over (partition by tr_id order by load_date), status), status) as status_new
    FROM mytable
    where tr_id='10001'

  9. #8
    Join Date
    Aug 2017
    Posts
    9
    Never mind Mark about my latest ask to get a generic sql. It's working well when I remove the tr_id=10001 and looks like updating through all tr-id in the table.

    Thanks Mark it's an amazing solution to me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •