I was able to get this to return the correct data.  Format File and data file are attached

Here's the sql

...

I don't know your data, but you are taking every record in the PCT table and applying it to every record in the ProductionCosts tables.  Does the PCT table represent...

There are a lot of articles on why float is not an accurate datatype to be using even on this site as well as Books Online.  With that said, how...

Looks like it is working properly.  It is summing based on the Week number in your table.  You'll need to define those 3 days as in the same week.

This doesn't make much sense.  Are the ranges hardcoded or variable?  how are they determined?  You can simply do a sum and a case when to handle it, but it's...

your DDL is incorrect.  The data doesn't match the sample query you sent.  Please provide relevant DDL and sample data and test it before posting

try this, it returns the AdvertisementID, but you can tweak as needed

declare @j-2 nvarchar(max) = '{
"property": {
"address": {
"councilArea": "KING",
"councilAreaId": 12345,
"isDerivedUnit":...

This doesn't make any sense.  The data doesn't help with what you want.  Can you explain?  This is what the sample data produced

ID    Name    Budget_Day    DateCreated         ...

These 2 things look wrong to me

This is getting count of all records from CTE regardless of ID
TempCount AS
(
SELECT

Can you step through it in the debugger?  Does the statement you created run successfully?  Lastly, try putting the Exec (@SQL) in a try catch block to see if there...

what datatype is PolicyExpirationDate?  Converting the null to datetime shouldn't be an issue.  See below.  Can you reproduce it in some sample data?

drop table if...

have you tried looking in msdb?

If the fields aren't there (i.e Field4), then the entire row won't return.  You can use a Left join instead of a Join to return missing rows and then handle the...

Since it's being called from multiple places, the only way to get it is to pass it in as a parameter

I'm not sure why you need ServerName, since you could have potential issues with linked servers.  To get the info for each db, you can use sp_MSforeachdb.  Something like