Here's the code if a month's weeks start in the prior month:

SELECT

WeekStartDate,

WeekEndDate,

LEFT(DATENAME(MONTH, WeekEndDate), 3) + '_Week' +...

The WeekName logic contradicts itself. If:

8/30/2015 9/5/2015 = Sep_Week1

then shouldn't:

9/27/2015 10/3/2015 = Oct_Week1 rather than "Sep_Week5"

It's impossible for that week to be both Sep_Week5 and Oct_Week1 ... or...

From a performance standpoint, do this, get rid of the functions against the column in the WHERE clause to allow max possible use of all indexes. Always avoid functions...

drew.allen (9/30/2015)

---

Part of the reason that XML string splitters are inefficient is converting character separated strings to a format...

jguffey (9/30/2015)

---

Thank you both for the replies.

Scott, can you explain dbo.tally and t.tally. I'm not sure I understand what they are or what I need to do to create that...

Yikes. Here's an alternative that should perform better for you:

select STUFF(

cast((select ' ' + SUBSTRING(spt.notes, t.tally + 3, CHARINDEX('</p', spt.notes, t.tally + 3) - (t.tally...

You need to CAST another column to varchar, since it's an int, as was done with billpay:

select '"'+cast([ssn] as varchar(11))+'","'+ [username]+'","'++'","'+cast([estatement] as varchar(100))+'","'+ CAST([billpay] AS varchar(12))+'",'from @people

Carefully review what the best clustered index for this table would be. It's extremely like that all, or some, of the keys in your planned new index should actually...

You're right. I hadn't search thru the Profile settings before, but time zone was in there, and was somehow wrong for my location.

NOT EXISTS might also be an option here.

I don't see anything in the original code that indicates that Blocked or Doc Type must match in the CR Log, but naturally...

Unfortunately I can't get the files from my web link at work due to internal issues (seemingly neverending!).

Rather than actual partitioning, try clustering the table on CustomerID, start date and...

Not if: (1) you have software assurance on the active node and (2) it is truly passive.

From MS's licensing docs [emphasis added by me]:

"

Beginning with SQL Server 2014, each active...

SQL's priority is to use the index with fewer pages, because of less I/O which equal less overhead. Thus, to link FKs to the clustered index, you need to...

You would need an explicit transaction. In fact, the ROLLBACK itself will give you an error unless a transaction is active, so even with an explicit trans, you need...

SELECT LEFT(column_name, CHARINDEX('_', column_name + '_') - 1) AS before_underscore,

SUBSTRING(column_name, CHARINDEX('_', column_name + '_') + 1, 2000) AS after_underscore

FROM table_name