Join a table where the max value is less than or equal to joined table

[marley1978]

Hi,  I have spent most of the day searching for a solution and cant seem to find anything.

I am trying to determine an employees position code based on a point in time

My tables are

Table1

Month Date	EmployeeID
12/2016	1
1/2017	1
2/2017	1
3/2017	1

Table2

Table 1

Month Date	EmployeeID	JobCode
12/2016	1	A
2/2017	1	B
3/2017	1	C

I want to display for 1/2017 that the employee jobcode is still "A" and since 1/2017 is not in table2 I cant get it to work

Thanks in advance

[marley1978]

You still have employee 1 as having the job code of "Z"

[OmarBenSalem]

lol; my bad; I shared with u the wong script !

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate,

date(Date#(MonthDate,'MM/YYYY')) as NewDate,

EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1

12/2016, 2

1/2017, 2

2/2017, 2

3/2017, 2 ];

Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate,

date(Date#(MonthDate,'MM/YYYY')) as NewDate,

EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2012, 1, A

2/2015, 1, A

3/2017, 1,  C

12/2016, 2,Z

3/2017, 2,   C

];

NoConcatenate

FinalTable:

load *, num(NewDate) as numMonth  ;

load

MonthDate,NewDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth,

if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode

Resident FinalTable Order by EmployeeID,numMonth;

Drop Table FinalTable;

[OmarBenSalem]

so? Is it good? Can I go?..

It's being late and I must go (10pm..) But.. I want to make sure that we have figured it out

[marley1978]

I think so.  I will work on it.  It looks like its now displaying the 2012 dates when the main table starts at 12/2016

Its not that important for you to stick around.  I really appreciate your time and effort on this.

[david_smee]

Thanks Omar,  looks like you changed the JOIN,  I can work with this.  Thanks for your time with this. 

[OmarBenSalem]

u're welcome !

I guess you should change the last answer as the correct one; so that future searchers for such a problem would know what's the correct answer and what was just useful?