Given that the table had no index at all, I didn't think in this case that making the clus index unique was necessary, particularly to the...

I posted a simplified query if you don't need to list/modify the individual values.  To just subtract out the excluded values, you don't really need the CROSS APPLY:

            
January 16, 2020 at 4:47 pm
            
#3716045

            
            
                                                
        

Given that the table had no index at all, I didn't think in this case that making the clus index unique was necessary, particularly to the extent of...

Given that the table had no index at all, I didn't think in this case that making the clus index unique was necessary, particularly to the extent of adding the...

Maybe use a separate view for each language you need.  The view itself would be static but would be generated dynamically.

If you want to try this approach, and want further...

looking at your execution plan, 9 million rows at 15 bytes per row is not very large, so it was able to read the entire table and do...

SELECT 
    OT.Stuff1,
    OT.Stuff2,
    [...],
    TotalStuff = TotalStuff - 
     ...

Shouldn't matter, unless it's part of a (much) larger transaction.  A rowlock (at least) will be required to do the UPDATE anyway.  But you could remove it, just in case.

Much...

I'll assume that:

1) you have some way to get the OrderType from the OrderID since you'll need the Type to join to the Buck bits table as it's given

2) you...

Thanks for the DDL.  Yeah, the standard format of:

WHERE TransactionDate >= CAST(GETDATE() AS DATE))

will work just find for finding dates >= today at midnight.

Funny I actually tride to do a...

I don't see how OPENQUERY would provide any performance benefit here.  But, no, you can't create a temp table using OQ.

If you can provide more details of your specific situation,...

Don't limit yourself to a single format.  Just CAST it to date: if it's a valid date in any format, you'll get a date back.  If not, you'll have to...

That seems like a slick solution.  The...

What is the data type of the TransactionDate column?  If it's in the date/datetime category, then this condition:

where [TransactionDate] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

should pull all rows on or...

SQL can't execute the query as you've written it because there may be multiple values of T1.LineNUM for a given combination of DocEntry and ChapterID.

For example, suppose these two rows...