I'm thinking you're gonna have to...

You really need only a single ROW_NUMBER function:

SELECT

MAX(CASE WHEN row_num % 3 = 0 THEN LotNo ELSE '' END) AS Lot1,

MAX(CASE WHEN...

Yep, looks great, thanks!

Here's my scripted recommendations for index changes/rebuilds. I don't have time right now, but can explain more later if/when you have qs. Hope this helps!

------------------------------------------------------------------------------------------------------------------------

--Table:...

Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.

I suggest using SSIS to import that data. It will of course...

You could go the redo route if you could afford some brief downtime. Stop replication, delete the data and rebuild indexes, then re-initialize the replication with a fresh snapshot.

Quite right: if DFUR1.[AsOfDate] is any date/datetime type, you don't want to use ISDATE() on it.

Also, you can simplify the year and month comparisons:

AND

YEAR(MD1.[Inception Date])...

What percentage of the data does the part to be deleted represent? For example, if the database has 100 years of history, then 2 years is a relative pittance....

I think you need to step back...

And I realize that an absolute rule is not the intent of what you're doing, but that is what very often happens now. Identity keys have become so much...

But that's the problem that is over-looked in the all-out desire to achieve a "small clustering key". Often the "always a small clustering key" approach requires creating far more...

I think that Query1 data got scrambled or something.

Can you use a spreadsheet instead? After you run the queries, in the Results/Output area, left-click in the empty box...

Another statement of fact:...

The Dixie Flatline (7/15/2014)

---

Scott, I respectfully have to ask for clarification. Whenever you add multiple columns to the clustered index, you increase the size of ALL nonclustered indexes...

You don't need to explicitly convert it. Just get the format to a string of YYYYMMDD, which is always a valid date/datetime format:

SELECT DATEADD(HOUR, @hour, CAST(@date AS varchar(8)))

Here's a few thoughts:

If T270.C18133 might often be less than 19 bytes, add this to the WHERE clause:

WHERE

LEN(T270.C18133) >= 19

Since you don't have an ending %,...