WRACK (9/20/2010)

---

I have modified it a little. TOTAL needed to be excluding today as it mentions in the column header of the example data in the first post so AVG...

This isn't pretty, but it works:

SELECT MatrixName, [MON] AS 'MON', [TUE] AS 'TUE', [WED] AS 'WED', [THU] AS 'THU', [FRI] AS 'FRI', [TODAY] AS 'TODAY'

, [mon]+[tue]+[wed]+[thu]+[fri]+[today] AS SumCnt, ([mon]+[tue]+[wed]+[thu]+[fri]+[today])/6 AS...

I felt nice... and yes, that's what I was looking for. 🙂

CREATE TABLE #Rolling5Summary

(MatrixOrderINT,

MatrixNameVARCHAR(50),

OriginalDateDATETIME,

[DayOfWeek]VARCHAR(50),

TotalCountINT

)

GO

INSERT INTO #Rolling5Summary

SELECT 1,'Mail In',CAST( '08/16/2010' AS DATETIME ), 'MON', 540 UNION ALL

SELECT 1,'Mail In',CAST( '08/17/2010' AS...

Since you didn't post the execution plan, I'll ask the next question. 🙂

Is there a difference in the execution plans between the two queries when you run them? Devil...

Any chance you can post the original query?

phil.layzell (9/19/2010)

---

We have recently upgraded from 2000 to 2005 and the following issue has occurred with one of our SP's.

This worked fine in 2000;

"(@ReferenceNumber is NULL OR ( ISNUMERIC(P.ReferenceNumber) >...

swethak13 (9/19/2010)

---

Is there any standard approach in the ways to administer the SQL Server databases. As I am new and have no experience in these I don't know where to...

My preference has, and almost always will be, SPROC if there's logic involved if I can get away with it. Primarily so that I only have to re-deploy a...

BarkingDog, the reason Jeff's solution is better then mine/ours is speed. His update method gets in and out in one action, whereas the select/update pair takes two. You...

I've got the advantage, I'm west coast, so it's not beddie bye time for me yet, so I'm still...

Jeff Moden (9/19/2010)

---

Ah... to be sure... the "Kalpa Shah" that I refer to in the revision history was one of the two Systems DBA's I was working with on this...

Actually, that was me. Jeff's solution is much more elegant.

Ah, that makes sense, you're avoiding the necessity of the double call by using the running total method that's undocumented.

Btw, if you force the tablockx, you won't get...

Jeff Moden (9/19/2010)

---

Dang... I reviewed an article for someone on Sequence tables and now I can't find it.

At any rate, the WORST thing you could possibly do is use a...

You should be able to build a maintenance plan to do all that for you, and then automate it via SQL Agent.