The following will order the compsite alphabetically but it will have an unique value:

;WITH Composites

AS

(

SELECT A.ColA, A.ColB

, STUFF(

(SELECT ' ' + CAST(B.ColC as varchar(255)) + ','

FROM YourTable B

...

In Theory, it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.)

I seem to remember someone doing a...

You need to give the columns in a derived table a name so do something like:

SELECT COUNT(*)

FROM

(

SELECT UPPER(s_name) AS s_name

FROM bmi_tr

) dual;

or:

SELECT COUNT(*)

FROM

(

SELECT UPPER(s_name)

FROM bmi_tr

) dual (s_name)

Of course, you will...

The log file is only emptied after a log backup.

So:

1. Schedule log...

Maybe:

;WITH AttemptOrder

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY CODE, FAMILY, DEVICE, MANUFACTURER ORDER BY ATTEMPTS DESC) AS RowNum

FROM codes_table

)

DELETE AttemptOrder

WHERE RowNum > 1

I would test both solutions and see which is most efficient with your data.

Why bother with functions?:

DECLARE @starttime int = 80010

,@endtime int = 161500

SELECT ((@endtime/10000 * 60) + (@endtime%10000/100))

- ((@starttime/10000 * 60) + (@starttime%10000/100))

Just do another self join and sort it from there.

Something like the following:

DECLARE @DefaultMoisture float = 15.0

,@DefaultCu float = 2.0;

WITH ShiftTonnageMoistureCu

AS

(

SELECT S.[LogTime] AS ShiftTime

,S.Value AS WetTonnage

,COALESCE(AVG(M.Value), @DefaultMoisture) AS MoistureP

,COALESCE(AVG(C.Value), @DefaultCu)...

Maybe:

DECLARE @time int, @dt datetime;

SET @time = 80010;

SET @dt = DATEADD(second, 80010%100,DATEADD(minute, 80010%10000/100,DATEADD(hour, 80010/10000,'19000101')));

SELECT @dt

A CTE, except for the special case of recursion, behaves just like a non-persisted view that can only be used in the currect statement.

ie Just think of a CTE as...

A self join should work.

Something like:

-- *** Test Data ***

CREATE TABLE #t

(

[TimeStamp] datetime NOT NULL

,IDNumber int NOT NULL

,Value decimal(19, 2) NOT NULL

,Period varchar(2) NOT NULL

)

INSERT INTO #t

SELECT '20110115 07:00:00', 33080,...

Try something like:

SELECT *

FROM Client C

JOIN [Address] A

ON C.ClientID = A.Address_ClientID

LEFT JOIN

(

ITReturn R

JOIN

(

SELECT R1.ITR_ClientID, MAX(R1.AssesmentEndYear) AS AssesmentEndYear

FROM ITReturn R1

GROUP BY R1.ITR_ClientID

) D

ON R.ITR_ClientID = D.ITR_ClientID

AND R.AssesmentEndYear = D.AssesmentEndYear

)

ON C.ClientID =...

You could also have some problems with the data if you are not handling exceptions correctly.

(eg Attempting to put NULLs into a NOT NULL column etc)

You could try logging any...

I would be inclined to look at:

1. Just using union all for the default result set:

SELECT TOP (1)

{actual field list}

FROM {same as EXISTS statement}

WHERE {same as EXISTS statement}...

Google Number/Tally table.