I don't know your database, but I'm curious about the inline SQL. Since you are not summarizing anything and appear to just be limiting records, have you tried rewriting...

It's not the coalesce statement doing that. It's the structure of the SQL statement

The issue is setting a variable equal to itself plus a...

I believe the concatenation limitation comes from the definition of the varchar field in the column.  I could definitely be wrong about that.

The example you show here is concatenating the values...

Coalesce does exactly what you describe.  It returns the first non null value encountered and a null value if there is not a non-null value found in the parameter string.

The...

To my knowledge, Query Analyzer for SQL Server 2000 will not connect to a SQL 2005 database.  I could be wrong, but I'm pretty sure it can't.

First...why can't you use temp_tables or identify functions?  They are the natural solution.  Create a temp table with the same structure, pass a distinct listing of the records to the...

Two risks that I see:

One is no transaction so if a timeout or other issue happens you could end up with duplicates.

Two, yes to my knowledge you could end up...

The CASE statement is used with the SELECT command.  Just add Select in front of the CASE and you'll be all set.  Or rewrite it to use the IF command.

Happy New Year!  Excellent!

Always glad to help.  There are always so many options and variations that finding a solution can be much...

if you embed it into a function you'll just use it very much like a field name...such as:

[owner of function].[functionname(parameters)] as Alias

example might be:

dbo.fnProcess('Eng') as Process

Have you thought of getting the cartons with an inline query instead?  I've seen this improve my queries from time to time.  Frank K might have some info on if...

I was offering an option for the trouble of getting yesterday's records.  I didn't see anything referring to a large table or performance.

If DATEDIFF rules out indexes, does DATEADD?

Use the DATEDIFF function instead of all the DATEADDS.  Without modifying your code too much try this: (i removed the inner distinct as recommend earlier)

select distinct b.shipment, MAX(b.scandate)

from

     (select...

I would suggest you rewrite your SQL into ANSI accepted standards.  Using commas instead of explicitly using JOIN and since you are using commas you don't use the ON clause. ...

You can join a table to itself, but you really want to join to versions of the table.  I believe the following:

select * from (

(select LDGR_BAL_AM as TRADE1 FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP

WHERE...