You could unpivot both tables and inner JOIN them.  Here's an example based on another forum question/answer.  Something like this

drop table if exists #tTableA
go
create table #tTableA(
 ...

It was very quick, and I knew about it yesterday, but didn't really do much other than give a vote to try. Still amazed it happened.

Comments, suggestions, feedback on...

The queries ends up wrapping a DataReader in .Net 5, which although technically are created to handle SELECT statements better, still works perfectly for also INSERT/UPDATE/DELETE statements, but you...

It's an interesting approach.  There are a few questions that jump out tho.  There's no mention of how DML statements like INSERT/UPDATE/DELETE are handled?   Could it execute stored procedures?  How...

You know that the LAST_VALUE() is NEVER going to be before the CURRENT ROW, so it's completely unnecessary to specify the beginning of the window as UNBOUNDED PRECEDING when...

This is sort of confusing because the ORDER BY of the outermost query includes the 'head' column as well as 'Trans_ID'.  The SUM OVER ORDER BY is only by '(select...

This comes really close.  It uses a FULL JOIN which is functionally both a LEFT JOIN and a RIGHT JOIN.  It seems the example Status_ToolType column might have some consistency...

Should've hit refresh 🙂

Maybe the correlated subquery in the WHERE clause is converting your LEFT JOIN into an INNER JOIN.  Maybe try expressing OBS as OUTER APPLY SELECT TOP(1) ... instead.

Why call it "spoofing" and not "mocking"?  Is spoofing mocking?  Is mocking spoofing?

Instead of approximating the "current" date by selecting the MAX(date) the code could reference CAST(GETDATE() as DATE) to access the system datetime and cast it as type DATE.

This reply was modified 5 years, 2 months ago by Steve Collins.