As of this year

[#1]

I have a unique situation to figuring out at this time of the year "which department" he was working...

Dept_dt Name,year,key

01/0/1/2014,efi,2014|ca

06/02/2012,print,2012|ca

01/01/1900,impr,1900|ca

emp, hired_dt, name

0076,01/01/2004,Frank

I have date dimension...When I click on 2004 it should pop-up in a pivot that he worked for second record from department ie 2012|ca

tried year comparisons to figure out the closest date ie 2004 is close to 2012.

any help is appreciated...

[Anonymous]

Venkat,

Without any association or link or connection how this is possible??

[#3]

the association is with thru emplid to a common table (job) and the date is related to it..

I was trying to do is...if the 2004 is closer to 2012 then get me the second row...ie by comparing the dates..

hope I am making sense...

[#4]

I dept_dt<=hire_dt it is going to me two values..ie 1st and 2nd

01/0/1/2014,efi,2014|ca

06/02/2012,print,2012|ca

but 01/01/2004 is closer to 06/02/2012 ...not sure how to resolve this..need help

[Anonymous]

Venkat,

Pls share the sample data. On What basis you are saying 2004 is close to 2012??

[#6]

does the intervalmatch work here?

[#7]

as soon I click on the emplid and 2004 it should get the 2012 data from dept..

attached two excel sheets...

thanks for your help