Ok, there are a few points here.

1) The performance problem isn't surprising, since the potential approach I mentioned isn't going to perform well (as I noted originally).

The problem with your query as it stands is the scope of your 

t2.[brand]='sanyo' and t2.[condition]='used'

Right now you have that as part of the WHERE...

To directly answer your question, it should not work with OR. Let's step through the logic 🙂

"X OR Y" evaluates TRUE in the following cases:

1) Only...

Ok, so I finally got a chance to look at this again.

There are a couple ways of modifying the strictly-speaking-unsafe queries to make them safe that don't result...

Just based on what you've said here it sounds like making that column in the destination table an IDENTITY column would be the simplest.

If there are reasons that...

Yes, you must specify WITH (SNAPSHOT) on the table to use that isolation level with a memory optimized table.

So:

set transaction isolation level repeatable...

I'm curious about the desired results.

Particularly, why is the conditional_cumulative for ID=1 given as 0, but for ID=6 it is NULL?

I don't see any differences between...

To just add to what others have said, the BETWEEN is working correctly.

You're storing these values as strings, not numbers.

It's not that trailing zeros are being...