Even simpler

ALTER FUNCTION TruncDate

(

@InDate DATETIME

)

RETURNS DATETIME

AS

BEGIN

RETURN DATEDIFF(DAY, '19000101', @InDate)

END

Yes you can invoke same CTE several times in immediate statement after CTE definition.

SELECT *

FROM cte AS v1

INNER JOIN cte AS v2 ON v2.pk = v1.pk

WHERE v1.Col2 = 'Peso' AND...

Well... At least OP can find the answer "Huh?" and actually pick up Books Online and read about OUTPUT.

You can "cheat" if OUTPUT operator will do.

Why count every date from Tally table when every 7th will do?

set statistics io on

-- Ryan

select Date from (select dateadd(d, Number-1, '20080106') as Date from dbo.Tally) a

where datename(weekday, Date) =...

1.0 * b.[total shipping days] / c.[shipping days] AS ...

A SQL Server 2000 approach

DECLARE@Sample TABLE (ValueID INT, GroupID INT, ValueDate DATETIME, Amount INT)

INSERT@Sample

SELECT1, 1, '2008/05/19', 100 UNION ALL

SELECT2, 1, '2008/05/05', 20 UNION ALL

SELECT3, 1, '2008/04/03', 30 UNION...

INSERT TargetTable (description, client, contact)

select description,

xml.value('(Data/Client_Name/@value)[1]','varchar(200)') as client,

xml.value('(Data/Contact1_Name/@value)[1]','varchar(200)') as contact1

from FileStore

where description = 'Invoice_10013'

You're so funny Sergiy!

Maybe Mr Lester didn't design the sucker at all?

Maybe he was just assigned to save the application?

Your kind of arrogance is just the one I can do...

If there can be no duplicate Code1-Code4 values for any single record, try this

SELECT * FROM @t

DECLARE@Code1 CHAR(6),

@Code2 CHAR(6),

@Code3 CHAR(6),

@Code4 CHAR(6)

UPDATE@t

SET@Code1 = Code1 = COALESCE(Code1, Code2, Code3, Code4),

@Code2 = Code2...

Sergiy, did you test the code? It is not that solid at all.

Try this test data

SELECT...

DECLARE@Sample TABLE (RowID CHAR(16) PRIMARY KEY CLUSTERED, Code1 CHAR(6), Code2 CHAR(6), Code3 CHAR(6), Code4 CHAR(6))

INSERT@Sample

SELECT'A0', null, null, null, null UNION ALL

SELECT'A1', '12', null, '18', null UNION ALL

SELECT'A2', null, null, 'G9',...

A lot of UNION ALL going on!

1) Insert all UNION ALL queries into a temp table

2) Index properly

3) Use rest of query as is, but referenec to temp table instead

Here is another approach not using JOIN.

SELECTGUID,

MIN(TableName) AS TableName

FROM(

SELECTGUID,

'TableA' AS TableName

FROMTableA

UNION ALL

SELECTGUID,

'TableB'

FROMTableB

UNION ALL

SELECTGUID,

'TableC'

FROMTableC

) AS d

GROUP BYGUID

HAVINGCOUNT(*) = 1

Are you really sure

Select 2,'052K', NULL UNION ALL

is a bad data point?

It could actually mean a negative number, "-520".