Follow case – AsOfMonth ? Match ? Other ?

[#1]

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.

[fosuzuki · Accepted solution]

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;

[#3]

You are an absolute genious ! Never mind the ugliness, it works like a charm (at least on test data). Thank you ever so much