So if the request is made between start and transfer1 (REQUEST_TS 1) it has the startDivision ( CB1 ), if it's between Transfer 1 and 2 (REQUEST_TS 2) it's the division in the transfertable for the first transfer (CB 5). if it's between Transfer 2 and the END (REQUEST_TS 3) it's the division in transfertable for the second transfer( back to CB1).
It's also possible there are no transfer then it's the startDivision.
How do i create a table with these tables were i can give each request a correct division and will it be possible to do this on big files?
If I've understood the question, the trick isn't so much the intervalmatch itself, but rather in setting up a table structure to use with the intervalmatch. I think you need to generate one that looks like this:
Once you have that, it's easy enough to do the intervalmatch:
LEFT JOIN (REQUEST) INTERVALMATCH (REQUEST_TS,CODE) LOAD Start ,End ,CODE RESIDENT DivisionAssignment ; LEFT JOIN (REQUEST) LOAD * RESIDENT DivisionAssignment ; DROP TABLE DivisionAssignment ;
To build the table, you're going to need to combine the data from your START and TRANSFER tables. I could probably figure it out, but I'm short on time today, and hoping you can figure it out on your own.
That's indeed the way i want it to be! Now i need to find how to combine them correctly. So i need to start with the startTS from the START table and then depending on how much transfers there are i have to build time periods.
ID startTS --> transferTS 1 startDivision ID transferTS 1 --> transfer TS 2 transferDIVISION 1 ID transfer TS 2 --> transfer TS 3 transferDIVISION 2
ID transfer TS 3 --> endTS transferDivsion 3
I probably need to use for loops but how can i say, combine startTS with transferTS 1?