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

    Follow case – AsOfMonth ? Match ? Other ?

    Annette Søgaard

      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.

        • Re: Follow case – AsOfMonth ? Match ? Other ?
          Fernando Suzuki

          Kinda ugly, but I think it works:

           

          Cases:

          LOAD * inline [

          Case_ID, Reg_date,    Product

          1111,      15-03-2012,  0

          2282,      16-06-2012,  1

          3363,      30-09-2012,  2

          4444,      30-09-2012,  1

          ];

           

           

          Left Join

          LOAD * inline [

          From0To1, RS_date,    Case_ID

          1111,          15-03-2012,  2282

          ];

           

           

          Left Join

          LOAD * inline [

          From1To2, Ebev_date, Case_ID

          2282,          16-06-2012, 3363

          ];

           

           

          Left Join

          LOAD * inline [

          From0To1_, RS_date_,    From1To2

          1111,          15-03-2012,  2282

          ];

           

           

          Left Join

          LOAD Case_ID,

                     alt(RS_date_, RS_date, Ebev_date, Reg_date) AS Date

          Resident Cases;