The "U" lock type prevents unnecessary blocking (and deadlocking). It will get converted to an exclusive lock once you actually update the row, but, until then, others can still...

Good, but I suggest also stripping the time off the date. Otherwise you could get different results at different times of the same day, which could drive you crazy...

Btw, I'm Scott.

The SSC they show is for the site itself, SqlServerCentral 🙂

Assuming that the string

'APARTMENT'

is always in that column.

Otherwise you need to test it, and if not present, change the result so you don't pass -1 to a SUBSTRING function.

You're getting a length of -1, which is invalid for SUBSTRING.

You need to use "PropertyName1" and not the alias name in your SUBSTRING -- 'Property' is a literal.

SELECT

A.PropertyName1 as...

That's a monstrous GROUP BY, which are fairly high overhead.

Is there any way you can get the COUNT(*) from one of the original tables prior to JOINing, so that you...

You could also do something like this:

CREATE TABLE combined_table_for_export (

id int identity(1,1) not null,

case# <datatype> not null,

sec_seq# int...

Are the SubCategorys the same for every EffDate? Are they consistent across the data?

As mentioned already, assuming a proper index -- (name, date) INCLUDE price, in that order -- code below should work OK, although I still don't like how poorly the optimizer...

Yeah, sorry, I knew the first CTE was a killer, but I just didn't have any time today.

Sorry about that. I don't have much time to look ... I just do this for a quick minute or two when I need a break. This is...

You do need at least two slashes in all data for my previous code to work. If you may not have 2 slashes, you can do this:

SELECT

...

delete from SHIPPING_STG

from SHIPPING_STG ss

inner join (

select acct, item, max(sdate) as maxDate, min(sdate) as minDate

from load_shipping

group...

NOTE: This may be obsolete by now, I got very busy and just now able to spend a quick couple of minutes on this.

Please try this, testing it on a...

[n/a, removed]