Merge two historical tables, please help...

[peac3]

Hello all,

I have a question trying to merge two historical tables which the dates are not aligned.

I would like to merge the historical company table active start and end date
with the most recent address within the date range based on the enddate.

example below:

Company table

CompanyId CompanyName                            StartDate       EndDate                  
1	APPLE	                                 2016-05-01	2016-12-31
1	APPLE	                                 2017-01-01	2017-05-31
2	GOOGLE	                                 2017-06-01	2017-06-30
2	GOOGLE	                                 2017-07-01	2017-08-31
2	GOOGLE	                                 2017-09-01	2017-09-30
3	FACEBOOK	                         2017-10-01	2017-12-31
3	FACEBOOK	                         2018-05-01	2018-10-31

Location Table

CompanyId Location                            StartDate          EndDate                  
1	NY	                              2016-05-01	2016-06-30
1	LONDON	                              2016-07-01	2016-10-31
1	TOKYO	                              2017-01-01	2017-04-30
1	HONGKONG	                      2017-05-01	2017-05-21
2	SINGAPORE	                      2017-06-01	2017-06-15
2	BOSTON	                              2017-06-16	2017-06-20
2	LA	                              2017-07-30	2017-08-31

And the expected output would be:

CompanyId CompanyName                            StartDate       EndDate       Location                
1	APPLE	                                 2016-05-01	2016-12-31     london
1	APPLE	                                 2017-01-01	2017-05-31     hongkong
2	GOOGLE	                                 2017-06-01	2017-06-30     boston
2	GOOGLE	                                 2017-07-01	2017-08-31     LA
2	GOOGLE	                                 2017-09-01	2017-09-30     null
3	FACEBOOK	                         2017-10-01	2017-12-31     null
3	FACEBOOK	                         2018-05-01	2018-10-31     null

Any help would be greatly appreciated.

Thanks

 

[johnherman]

Please explain why APPLE does not match to TOKYO in your output. Is it because the first CompanyID int he Company table matches the first CompanyID in the Location table? And since there is not a third CompanyID in the Company table, there is no match to TOKYO?

Need to understand the "Rules" you are using to create the output.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[fredericofonseca]

I understand it like this

select co1.*
     , locs.location
from company co1
outer apply 
(select top 1 location
from companylocation cl1
where cl1.companyid = co1.companyid
and co1.enddate between cl1.startdate and cl1.enddate
order by cl1.enddate descending -- get the most recent entry for a given company 
) locs

untested

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[peac3]

fredericofonseca - it doesn't work

johnherman - it is because within the date range 2017-01-01 and 2017-05-31, hongkong is the latest location (end date = 2017-05-21)

 

[fredericofonseca]

peac - why does it not work?
do you get an error? if so what error.
does it give wrong results? if so what does it show and why is it wrong.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886