Join/Merge on date range from two tables

Good day. I have two tables, tableA and tableB. I thought I had it but apparently I am doing something wrong on my join.

TableA has a few dates with datapoints.
TableB has just a range of dates.

I want to merge or join both tables based on tableA's dates being between tableB' dates. How do I join tables based on each others tables dates? An help is most appreciated. Thanks.

Query

SELECT o.processval,
u.datepar
FROM @tblA o
LEFT JOIN @tblB u
ON u.datepar >= o.dateentered
AND u.datepar <= o.dateentered


tblA Data

processval dateentered
0 2018-07-26 12:47:18.480
14.7514 2018-07-26 12:47:18.480
27.2616 2018-07-26 12:47:18.480
26.7243 2018-07-26 12:47:18.480
60.8281 2018-07-26 12:47:18.480
63.4687 2018-07-26 12:47:18.480
60.9732 2018-07-26 12:47:18.480
68.0573 2018-07-26 12:47:18.480
0 2018-07-26 12:47:48.213
14.7555 2018-07-26 12:47:48.213
27.2591 2018-07-26 12:47:48.213
26.7345 2018-07-26 12:47:48.213
60.8154 2018-07-26 12:47:48.213
63.4356 2018-07-26 12:47:48.213
60.9401 2018-07-26 12:47:48.213
68.1057 2018-07-26 12:47:48.213
0 2018-07-26 12:47:48.483
14.7555 2018-07-26 12:47:48.483
27.2591 2018-07-26 12:47:48.483
26.7345 2018-07-26 12:47:48.483
60.8154 2018-07-26 12:47:48.483
63.4356 2018-07-26 12:47:48.483
60.9401 2018-07-26 12:47:48.483
68.1057 2018-07-26 12:47:48.483
0 2018-07-26 12:48:18.483
21.1506 2018-07-26 12:48:18.483
27.1649 2018-07-26 12:48:18.483
27.0706 2018-07-26 12:48:18.483
61.3094 2018-07-26 12:48:18.483
64.7775 2018-07-26 12:48:18.483
61.8161 2018-07-26 12:48:18.483
70.0078 2018-07-26 12:48:18.483
0 2018-07-26 12:48:48.487
22.8971 2018-07-26 12:48:48.487
27.0859 2018-07-26 12:48:48.487
26.9688 2018-07-26 12:48:48.487
70.9347 2018-07-26 12:48:48.487
68.5385 2018-07-26 12:48:48.487
67.7925 2018-07-26 12:48:48.487
72.6892 2018-07-26 12:48:48.487
0 2018-07-26 12:49:18.487
23.5001 2018-07-26 12:49:18.487
30.5184 2018-07-26 12:49:18.487
26.9102 2018-07-26 12:49:18.487
71.5178 2018-07-26 12:49:18.487
69.906 2018-07-26 12:49:18.487
68.867 2018-07-26 12:49:18.487
84.3287 2018-07-26 12:49:18.487
0 2018-07-26 12:49:48.490
23.9836 2018-07-26 12:49:48.490
36.8819 2018-07-26 12:49:48.490
26.844 2018-07-26 12:49:48.490
73.7739 2018-07-26 12:49:48.490
71.9634 2018-07-26 12:49:48.490
71.0493 2018-07-26 12:49:48.490
93.7401 2018-07-26 12:49:48.490
0 2018-07-26 12:50:18.490
24.6327 2018-07-26 12:50:18.490
44.1773 2018-07-26 12:50:18.490
26.8567 2018-07-26 12:50:18.490
76.1497 2018-07-26 12:50:18.490
74.3621 2018-07-26 12:50:18.490
73.4811 2018-07-26 12:50:18.490
99.4975 2018-07-26 12:50:18.490


tblB DATA

datePar
2018-07-26 12:47:18.060
2018-07-26 12:48:18.060
2018-07-26 12:49:18.060
2018-07-26 12:50:18.060
2018-07-26 12:51:18.060


Desired Results

processval dateentered
0 12:48:18:060
14.7514 12:48:18:060
27.2616 12:48:18:060
26.7243 12:48:18:060
60.8281 12:48:18:060
63.4687 12:48:18:060
60.9732 12:48:18:060
68.0573 12:48:18:060
0 12:48:18:060
14.7555 12:48:18:060
27.2591 12:48:18:060
26.7345 12:48:18:060
60.8154 12:48:18:060
63.4356 12:48:18:060
60.9401 12:48:18:060
68.1057 12:48:18:060
0 12:48:18:060
14.7555 12:48:18:060
27.2591 12:48:18:060
26.7345 12:48:18:060
60.8154 12:48:18:060
63.4356 12:48:18:060
60.9401 12:48:18:060
68.1057 12:48:18:060
0 12:49:18:060
21.1506 12:49:18:060
27.1649 12:49:18:060
27.0706 12:49:18:060
61.3094 12:49:18:060
64.7775 12:49:18:060
61.8161 12:49:18:060
70.0078 12:49:18:060
0 12:49:18:060
22.8971 12:49:18:060
27.0859 12:49:18:060
26.9688 12:49:18:060
70.9347 12:49:18:060
68.5385 12:49:18:060
67.7925 12:49:18:060
72.6892 12:49:18:060
0 12:50:18:060
23.5001 12:50:18:060
30.5184 12:50:18:060
26.9102 12:50:18:060
71.5178 12:50:18:060
69.906 12:50:18:060
68.867 12:50:18:060
84.3287 12:50:18:060
0 12:50:18:060
23.9836 12:50:18:060
36.8819 12:50:18:060
26.844 12:50:18:060
73.7739 12:50:18:060
71.9634 12:50:18:060
71.0493 12:50:18:060
93.7401 12:50:18:060
0 12:51:18:060
24.6327 12:51:18:060
44.1773 12:51:18:060
26.8567 12:51:18:060
76.1497 12:51:18:060
74.3621 12:51:18:060
73.4811 12:51:18:060
99.4975 12:51:18:060


The table A is using the values that are within the data range of tableB. Any help is most appreciated. Thanks.

2 Answers
2

You can try to use LEAD function get the next DateTime from tblB, then join with tblA by the during DateTime.

LEAD

tblB

join

tblA

SELECT processval,nextDt FROM tblA a
inner join (
select *,lead(datePar,1,datePar) over(order by datePar) nextDt
from tblB
) b on a.dateentered between b.datePar and b.nextDt


sqlfiddle

I needed a second datetime column for tblB for the last operand '<='. I could have went recursive; but the column already existed so I just needed to call it so I could compare against it.

'<='

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.