your question is a bit vague. can you please provide a full explanation?

if you question is this:

field in (select ... from ...)

is functionally identical to

field in (select distinct ......

when performing an outer join, criteria in the WHERE clause is performed after the JOIN has taken place. (this is a subtle but vital distinction.) in your case,...

just move your plant condition to the join.

select *

from company

left outer join

plant on plnt_companyid=comp_companyid and...

i updated the function to be an inline table valued function since...

here's a function that will do it.

use global

go

ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )

returns @returnTable table

( item varchar(2048) not null, itemSequence smallint not null )

as begin

declare @xml XML

set...

:peter

You 'da man!

scalar value results:

500 rows: CPU time = 844 ms, elapsed time = 879 ms.

15,000 rows: CPU time = 25531 ms, elapsed time...

:peter

ah. i missed the in-line requirement. then it makes sense, since the function would be just short-hand for a select.

Bob Hovious (2/5/2009)

peter (2/5/2009)

When dealing whith functions that are called frequently, it usually pays off to write them as inline table valued functions (also called parameterized views) and then invoke...

j (2/4/2009)

Granted. There's a reason the word "robust" doesn't appear in the article, while a form of the Latin "simplus"...

my take is like Muharsky's. non-working days are passed to the function rather than hard-coded so...

A common table expression (CTE) should help you.

; with Sequencing as (

SELECT [Key Columns], DuplicatingField,

row_number() over (partition by DuplicatingField

order by [DateTimeField] desc ) as sequence

FROM Reporting_Table...

Thanks for a very useful function. When applied to a scalar value, the routine is fine. However, if you're applying it to thousands of rows, it's very slow...

using format file for missing fields is explained very well in this article http://msdn.microsoft.com/en-us/library/ms179250.aspx

Lynn Pettis (12/15/2008)

Personally, I'd stay away from the smalldatetime data type. It may take up less space, but I'd be concerned with creating a "Y2K" issue that may not...

Appropriate datatypes depends entirely upon your application and data usage.

From your column name list, the only obvious choice is

ExecutioninstanceGUID should be a uniqueidentifier.

Executionstarttime could be either a...