I'm not sure that will work.  The CloseDatabase command takes source_database_name and target_database_name as parameters, so it creates a read-only copy on the same server.

144 million reads and 70 million reads??!!   How many rows are returned?  Have you looked at the execution plan?    This hits allresults twice, so doing rowcount twice

SELECT results.*,

(SELECT Count(*)

FROM...

can you provide DDL and sample data to help.  If they have multiple addresses but the same acct_id, then you can do something like

select Acct_id, count(1) from my_table group by...

sounds like the volume has increased and they need to be rebuilt more often

I would look here too

(convert(varchar, dateadd(hour, -6, sk.CreationDate),0) between @StartDate and @EndDate)

why converting dates to varchar?  This will not use an index.  I see @StartDate and @EndDate, but don't know...

what is the compatibility level of the db?

I think you're going to need a master proc that has this logic or SSIS.  Then the proc could call the procedure 4 times and pass in the TransactionIDs that...

If you provide DDL, samples data and expected results, we can help.  Not sure what you have and what you are looking for in your description

why would you need extra field in the join?  Shouldn't the primary key and Record Source be enough?

Target.[HistoryDataPrimaryKey] = Source.[HistoryDataPrimaryKey] AND --

Target.RecordSource = Source.RecordSource AND

then

when matched and Target.LastSeenDate > @LastSeenDate...

You can put it in a variable.  Just not sure why you would need the NotExists part.  What are you trying to accomplish?  I don't know how you would get...

Your SQL look right, but we don't have your data set, so it's hard to troubleshoot

Create table #t (ListDesc varchar(20), WeekOfMonth int, RecordCount int, MonthYearOf varchar(20))

insert into #t values

('EXP_PKG_1',1,100, 'April...

• This reply was modified 5 years, 7 months ago by Mike01.

Is it going to be impossible to discuss without sample data? If so, let me know and I will create a spreadsheet. If you have a test data generator...

do you have any sample data and expected results?

Please provide DDL and sample data. Pictures are great, but we cannot readily consume them to help with your query

Your example works because it can convert it to Decimal (32,6). It rounded the last value. The try convert will return null if it can't convert it. ...