So you're looking to match, one for one, your sales & refunds, and examine what's left?

This looks suspiciously like homework to me! However...here's half of what you need...

DROP TABLE #Widgets

CREATE TABLE #Widgets (TranDate DATETIME, Product VARCHAR(10), quantity INT, value MONEY)

INSERT INTO #Widgets (TranDate, Product, quantity,...

DECLARE @TheDate DATETIME, @HalfDayAndaBit DECIMAL (14,13)

SET @HalfDayAndaBit = 0.5000000385803

SET @TheDate = '2008-02-05 11:59:59.993'

SELECT @TheDate

SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)

SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 11:59:59.997'

SELECT @TheDate

SELECT...

DECLARE @TheDate DATETIME

SET @TheDate = '2008-02-05 23:59:59.997'

SELECT @TheDate

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 00:00:00.000'

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

😉

We're still on 2K, could be decades...

Running an update against a table with a little over 500,000 rows I get the following (average of three runs, in seconds)

1) CAST / FLOOR / CAST [2.32]

2) DATEADD /...

Not yet, Derek. Derek? Where'd he go? 😉

Have a play with the following, it should help you identify where the sp is failing:

DROP TABLE #Offboardv7

GO

CREATE TABLE #Offboardv7(

[ID] [int] NOT NULL,

[SID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LAST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS...

Skinning a cat innit:

SELECT [DateOnly] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- CAST / FLOOR / CAST

SELECT [DateOnly] = dateadd(dd,datediff(dd, 0, getdate()), 0) -- DATEADD / DATEDIFF

SELECT [DateOnly] = CAST(CAST(GETDATE()...

It's perhaps worth a look. The error message states something about nested cases, but case nesting isn't necessary for the error - just more than 10 options in one case...

This is probably a long shot Paul, but what is the maximum number of options you have in a single CASE construct? There's a limit, when running a query against...

You're very welcome Brian, and many thanks for the feedback.

Ian Yates (1/31/2008)

---

It will definitely be some sort of index rebuild. Another thing it could be is if you run a command similar to (but much larger in scope)

update...

Matt Miller (1/31/2008)

---

persnickety is a kind word to describe Lawson....:) But still - you may find it doesn't need that much maintenance.

Actually - I thought QWIRKY was the layout...

Good point Matt, I don't know the answer but your suggestion is pretty easy to check out. The system is Lawson which I'm finding out is kinda "pernickity" and quirky...

Recovery model is "Full"