You don't want to do the calculation twice if you don't have to.  Try this:

SELECT CONCAT(F.FloorWeight,'lbs ',(W.WeightinPounds - F.FloorWeight)*16,'oz') as PtWeight
FROM #T t
CROSS APPLY...

No, just subtract the integer part, which you get by using FLOOR.

John

Multiply by 2.2 to convert to pounds and multiply the resulting decimal part by 16 to get the ounces.

John

Those aren't the full definitions - you haven't included primary keys or any other constraints.  Since the error is a primary key violation, it's important to know something about the...

How often does this happen?  The best place to start, I think, would be to download sp_whoisactive (if you don't already have it) and run it next time tempdb is...

Without table DDL and sample data, we're just guessing.  However, this may be close to what you're looking for:

WITH NumberedbyMachine AS (
    SELECT
        RV.Name0,
        UCS.Status,
        UCS.LastStatusChangeTime,

I'm guessing the two correlated subqueries are slowing you down.  Each one is executed once for each row in your result set.  Please post your execution plan for help with...

You'll need to use a splitter function to break your strings down into their individual elements, something like this (not tested because I don't actually have the splitter...

What is the result of the second query?  My guess is that the first one takes so long because it has to return all the data to the client.  The...