It looks like I have resolved this with the query running in reasonable time and producing correct results.  Not sure if the final query is the most efficient but it...

Thanks for that.

The ERD is only 3 tables

With the tables and simple sample data in the following code:

---create assets_sc table

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[assets_sc](

[asset_id] [varchar](50)...