Join the two tables in between two field values

[#1]

Hello Folks, I am looking for solution how to join two tables in between field values.

TAB1:

ID	MARKETID	END_TIME	START_TIME	VALUE
11640	MPX26	8/14/2013 5:16:27 PM	8/14/2013 11:02:45 AM	22477
11642	MPX26	8/14/2013 2:14:11 AM	8/14/2013 12:47:01 AM	5226

TAB2:

MARKETID	TIME	WONUMBER	USER
PX26	8/14/2013 1:06:27 PM	P035687	x-schanta
PX26	8/14/2013 2:06:47 AM	P034676	x-david

I want join these tables on MARKTID and TIME (TAB2) must be fall in b/n START_TIME and END_TIME (TAB1).

OUTPUT:

ID	MARKETID	END_TIME	START_TIME	VALUE	WONUMBER	USER	TIME
11640	PX26	8/14/2013 5:16:27 PM	8/14/2013 11:02:45 AM	22477	P035687	x-schanta	8/14/2013 1:06:27 PM
11642	PX26	8/14/2013 2:14:11 AM	8/14/2013 12:47:01 AM	5226	P034676	x-david	8/14/2013 2:06:47 AM

Any thoughts, how to achieve this ?

Thanks in Advance...

[Anonymous]

TAB1:

load * inline [

ID,MARKETID,VALUE

11640,MPX26,22477

11642,MPX26,5226

];

TAB11:

NoConcatenate

load ID,right(MARKETID,len(MARKETID)-1) as MARKETID,VALUE

resident TAB1;

Drop Table TAB1;

TAB2:

load * inline [

MARKETID,USER

PX26,P035687

PX26,P034676

];

[Anonymous]

in terms of user please left(USER,len(USER)-2)

[#4]

Hi,

Please find the attached application

[#5]

Thanks Vikram. But I am working with very big tables.

Is there any way can I join in single load statement?

[Gysbert_Wassenaar]

You can use the intervalmatch function to match a value with an interval. See this blog post: IntervalMatch, and the document referenced at the end: IntervalMatch and Slowly Changing Dimensions