2 Replies Latest reply: Oct 26, 2012 4:20 AM by Annette Søgaard RSS

    Follow case – AsOfMonth ? Match ? Other ?

      I’m absolutely stuck here. I have 3 tables:

       

      RS (contains cases that have moved from product 0 to 1):

      RS_CaseID, RS_date,    Case_ID

      1111,          15-03-2012,  2282

       

      Cases (contains all cases whether moved from 0 to 1, from 1 to 2 or have always and only been in 0, 1 or 2):

      Case_ID, Reg_date,    Product

      1111,      15-03-2012,  0

      2282,      16-06-2012,  1 (this one has been moved from 0 and has a new date)

      3363,      30-09-2012,  2 (this one has been moved from 1 and has a new date)

      4444,      30-09-2012,  1 (this one has always only existed in 1)

       

      Ebev (contains cases that have moved from product 1 to 2):

      EbevCaseID, Ebev_date, Case_ID

      2282,          16-06-2012, 3363

       

      I need the following in the load script:

      If a case is moved from product 0 to 1, the case in 1 should keep the date from product 0.

      If a case is moved from product 0 to 1 and 1 to 2, the case in 1 and 2 should keep the date from product 0.

      If a case is moved from product 1 to 2 but never have been in product 0, the case in 2 should keep the date from product 1.

       

      So in short: keep the very first date always even if the case_id and date has changed in Cases table. Can anyone help ?

       

      NB: cases can only move from 0 to 1 and 1 to 2. No other combinations are possible.