SELECT C.Colo_ID, C.Colo_Procdate, X.Res_Date, X.Res_value
FROM #Colo C
CROSS APPLY
(
SELECT TOP(1) R.Res_Date, R.Res_value
FROM #Res R
WHERE R.Res_id = C.Colo_ID
AND R.Res_Date <= C.Colo_Procdate
ORDER BY R.Res_date DESC
) X;

You question does not make sense.

If you would care to post a sensible question (with consumable test data!!!!!) someone might give you a sensible answer.

-- *** Consumable...

• This reply was modified 5 years, 1 month ago by Ken McKelvey.

If you have not already read it, you may find the following to be useful:

https://www.red-gate.com/library/troubleshooting-sql-server-a-guide-for-accidental-dbas

An example of a nested join is:

FROM TableA A
LEFT JOIN
(
TableB B
JOIN TableC C
ON B.Bid = C.Bid
)
ON A.Aid = B.Aid;

which could be written like:

FROM TableA...

• This reply was modified 5 years, 1 month ago by Ken McKelvey.

Brackets are generally used to make nested joins more recognizable even thought it is actually the order of the ONs that matter.

As this is not a nested join, the brackets...

If anyone else is affected by this, it still occurs with SQL2017 CU22 on a test VM.

This post does not make much sense so I presume your query actually has a WHERE clause.

If the linked server has a different collation etc then the four part naming...

Also:

1. what is the point of x7? It is not referenced.

2. if the code is part of a stored procedure all those variables in the WHERE clause could produce parameter...

ps I think this is some sort of packing or loading algorithm but the details are not clear.

The logic seems to be that, for each Noseqtrt, the rank for an address with an even count has to start with an odd number.

This sort of logic is best...

• This reply was modified 5 years, 2 months ago by Ken McKelvey.

Also, this document is worth looking at:

https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

I have seen similar messages caused by intermittent network errors.

You could try speaking to your systems people but these sorts of errors can be difficult to tie down. It is...

You need to pivot the 'phone numbers.

Also it looks as though some of your outer joins should be nested.

Search for both of these.

If you want tested code you need to...

https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

The tally table works, as long as the use of the numbers is consistent, and scans the table once.

UNION ALL scans the table four times.