I suspect there's some way to do this without recursion, but for now, here's a method using recursion.

DECLARE @amount_to_spend decimal(9, 2)
SET @amount_to_spend = 50.00

;WITH cte_products_by_price AS (
...

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number UNION ALL
    SELECT...

I don't think you don't need an explicit COMMIT and ROLLBACK.  In SQL Server, by default each SQL statement that modifies data will be in its own transaction.

Specify "SET XACT_ABORT...

To the first point, there's...

One place to start is reviewing the "Top 10 Avg I/O Queries".  See if anything stands out there.

Next I'd review the index stats.  Looking particularly at missing indexes and the...

OK.  Sorry, my bad, it...

Didn't you already ask this q?!

Yes, there is a better way.  You can use CROSS APPLY(s) to assign alias name(s).

SELECT [Date Req Created], [Date Initiated], ...
FROM ...
CROSS APPLY (
    SELECT NULLIF(A.CALC_Date_EOIReceived,...

(1) Download SQL Server Developer Edition.  (Hint: it's free!)

(2) Get books by Itzik Ben-Gan (for T-SQL learning) or, for DBA/system learning, the "Inside SQL Server" books by MS.

(3) Do the...

Technically that's just a unique index,...

The indexes will come along.  Since they are part of the db, they get restored with it.

For more specific tuning, yes, we must see the query plan and the DDL for the table(s) involved, including all indexes.

Definitely not for the first one.  Bump that up to at least 40:

EXEC sys.sp_configure...

Note the "<" in the comparison; that is required.  This handles due_date correctly whether it is a datetime data type or a date.

;WITH cte_get_max_due_date AS (
 ...

--Removed, double post.