'Loose' (or positional)) date matching

[wbodger]

I have two sets of data. The first one looks like this

2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-03 00:00:00.000
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-10 00:00:00.000
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-12-02 00:00:00.000

and the second looks like this

2E5021-1APX                   	CUSHION & COVER ASSY          	FG	2014-11-07 00:00:00.000	2014-12-19
2E5021-1APX                   	CUSHION & COVER ASSY          	FG	2014-11-25 00:00:00.000	2014-12-23
2E5021-1APX                   	CUSHION & COVER ASSY          	FG	2014-12-05 00:00:00.000	2014-12-24

I need to match these two sets on the matching code (in this case 2E5021-1APX) and then in date order (so 2014-11-03 will match 2014-11-07, 11-10 will match 11-25 and so on). I have thought I could perhaps use a cursor to step thru or a forward only recordset to match on min date with the last used date dropping off after each match, but I was hoping to find a better solution. The problem I have right now is when I join these two record sets, rather than getting a combined three records, they cross multiply and I get 9 results since I can't match on date. I am hoping what I am looking for is clear, what I am hoping to get in the end is

2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-03 00:00:00.000	2014-12-19
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-10 00:00:00.000	2014-12-23
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-12-02 00:00:00.000	2014-12-24

where right now I am getting

2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-03 00:00:00.000	2014-12-19
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-10 00:00:00.000	2014-12-19
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-12-02 00:00:00.000	2014-12-19
[2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-03 00:00:00.000	2014-12-23
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-10 00:00:00.000	2014-12-23
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-12-02 00:00:00.000	2014-12-23
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-03 00:00:00.000	2014-12-24
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-11-10 00:00:00.000	2014-12-24
2E5021-1APX	DOOR-ASSY HEADREST	10340507	70	EA	2014-12-02 00:00:00.000	2014-12-24

Thanks,
willie

 

[fredericofonseca]

is the date on the second set always higher than the one on the first set? and what if you have 2 dates that are the same (both on first set and on second set)

To give one possible way

select *
from mytbl t1
outer apply (select top 1 *
             from my tbl2 t2
             where t2.id = t1.id
             and t2.datex >= t1.datex
             order by t2.datex
            ) t2

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

That did it (at least mostly). Thank you!

wb