If it's truly just (and always) 2020 that is/will be missing then just hard code a union all with 2020

If there are multiple years and/or some dynamic logic that determines...

2 years!

DBCC ShrinkFile  is pretty straightforward command, not sure how to make it more efficient. 

That's the point -- it's not great and we can't make it better.

Why do you want...

sp_tableoption [ @TableNamePattern = ] 'table' , [ @OptionName = ] 'large value types out of row' ,[ @OptionValue =] '1'

will force large data out of row.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/3134/sql-server-performance-comparison-storing-xml-data-inrow-versus-outofrow/

Create another job that runs every N minutes to monitor it, and execute msdb.dbo.sp_stop_job it if it's been running longer than the desired time.

The msdb.dbo.agent_datetime(run_date,run_time) function can be used to...

Thank you for including DDL, but please use the insert/edit code sample so that your code is readable.

Not knowing the data, just guessing based on names &...

Not an expert, but it looks like you might want to use Modern_Spanish_CI_AS collation (case insensitive, but accent sensitive -- so a = A but à <> a).

e.g., for a...

You have Json nested at least 9-levels deep with something like 31 different objects/attributes.

What do you want to return and how? Do you want the data flattened (in which case...

In order, you have a lot of columns that appear to be costs (or possibly quantities) that are defined as nvarchar rather than numeric/decimal.

If this is a vendor database or...

It looks like the  WITH syntax may be ANSI SQL-99 standard.

Joe Celko may be able to confirm/clarify.

This seems like a non-normalized design. Is there a relationship between categoryc  & categoryx in the same row? What is it?

If not (and probably even if there is), use a...

"OR" is good choice in this scenario, & conveys intent clearly. You could try unioning two separate selects, w/ test_ID in the where clause of one, & oldsome_cust_ID in the...

Yes, OUTPUT is the way you would handle multiple rows.

But that procedure, as written, will never insert more than one row. It is using individual parameters correlating to columns, not...

Since sp_InsertCasesintoCallLog is only inserting one row at a time, you can just use SCOPE_IDENTITY() to get the inserted RecID,  and then use that in the where clause for the...

text datatype is deprecated -- don't use it.

Cast as varchar(11) instead (11 is maximum # of characters in a negative int).

Do you have the msi or ConfigTool? It sounds like the extension also needs to be registered in the SSRS config files, & the license needs to be registered in...