It does matter.

I did not see here tests for...

Unfortunately CEO's buy software after successful demos running out of empty databases populated with several dummy sample items.

Poor things, nobody told them they need to tests on a base of...

Senthil T (2/26/2009)

---

Id 12345 (for 100) and 12350 (for 101) have unique reportname for that user.

The above query does work like I wanted. It does not update the record 12345...

CTE is just "C like" replacement for derived table.

UPDATE R

SET reportname = reportname + '-' + CAST(uniqueid AS VARCHAR(5))

FROM dbo.userreports R

INNER JOIN (SELECT userid, reportname

...

Why you don't update 12345 and 12350?

What makes them different?

Don't have time for article but I several times eliminated table/index scans by applying correct type to char constants.

To avoid even thinking about this problem I'm trying everywhere:

1) not to...

nathanb (2/19/2009)

---

Also realised my Broken Drop Table Statement was because I had the Schema (Dbo) included. On just doing a straight select * on information.Schema I realised what it should...

Wow, what's happened to dir?

With 1.3 m records such query will return result right before Christmas. 🙂

You definitely need a separate column for precalculated results for historical records.

May be it's reasonable to populate it...

Why would you need average for previous 12 months for EVERY RECORD AT ONCE?

Every time you probably need it only for one particular record.

And do you have proper index...

Such correlated subquery on a decent amount of data is gonna kill even quite powerful server.

This should not be perfect but at least not so painful:

SELECT a.ItemNo, a.Date, AVG(b.Value) [12mAve]

FROM...

Only thing you need is to make sure your UDF returns sequence number in line with each value.

Then you're OK.

Otherwise you need to create another UDF which will return sequence...

There is a very strong reason for not allowing to pass tables to procedures as parameters.

Text, varchar(max) allow you to pass a variable up to 2GB in size.

To accept it...

BTW,

there is a good explanation why you need Calendar table in one of the recent posts here:

Why you need table Calendar

What about public holidays?

DECLARE @NextWorkingDay datetime

SELECT @NextWorkingDay = MIN(Date)

FROM dbo.Calendar

WHERE IsWorkingDay = 1 AND Date > GETDATE()

SELECT ...

FROM ...

EXPC_SHIP_DATE = @NextWorkingDay