select a1. caseid, a1.datetransferred, a1.newowner

from AuditTrail a1 join

(select a2.caseid, max(a2.datetransferred) DateXfrd

from AuditTrail a2

group by a2.caseid) a2

on a1.caseid = a2.caseid and a1.datetransferred = a2. DateXfrd

Should do it (untested).  Don't see why...

Thank you everyone - sensible advice as usual - will pass on and discuss with my colleague.

Phil

Rather than using this

Set @NewKey=(SELECT MAX(SRid) FROM FG_ID)

Use this

Set @NewKey= scope_identity()

Check out this excellent article for more info on this:

http://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp

Regards

No problem helping - post away!

Maybe I missed what you are trying to do.  The code I provided changes a character string from DDMMYYYY format into a format that can...

Sounds like your diagnosis is correct - you need to be comparing dates with dates.  Can you change the design on the SQL Server side?  Either change the datatype of...

I suspected that you would say something like that ... but then why does the CREATE TABLE solution work (just tested it)?

Why does it matter whether it exists, if the DELETE block is not going to be executed?

Not tried it, but I think that the error would not appear if the script were run when TableA existed and had the extra field.

SQL authentication requires more user admin than Windows authentication - but in a mid-sized company that might not matter so much, as it's not that much more when there are...

If you replace your SELECT * INTO and ALTER TABLE statements with a single CREATE TABLE statement containing the new column and constraint, your problem will go away.  But why? ...

Just following this one up.  So this query:

SELECT a.Municipality_No, a.Id_No

FROM TableA a

WHERE not exists (select x.Municipality_No from TableX x join TableA a on x.Municipality_No = a.Municipality_No)

Is faster than this one

SELECT Municipality_No,...

Is it about time that your users started using something like Access instead?

With that much data (as you've found), Excel just starts getting ungainly.

The Excel row limitation is way more than 10,000 - 65,535 rings a bell - so I am surprised that you are getting an error.

It may be possible to do all of this in one step through DTS, but my DTS is not good enough to know.  What I would do is

a) Create a...

Is the primary key an identity field?  If not, you need to populate it (uniquely) as part of the import - otherwise SQL Server tries to create multiple records with...