Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
You are an absolute genious ! Never mind the ugliness, it works like a charm (at least on test data). Thank you ever so much