Cheers Jeff.

SELECT a.UserID,a.PackageID,a.DateCreated

FROM Test a

WHERE EXISTS (SELECT * FROM Test b

WHERE b.UserID=a.UserID

...

Congrats on winning challenge 19!!!

Try using NULLIF, replace

(oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100 END ) ) * 100

with

(oe_line.unit_price - oe_line.commission_cost) / NULLIF(oe_line.unit_price,0) * 100 END ) ) * 100

Try this

CREATE FUNCTION dbo.InsertCommas(@s VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

SELECT @s=STUFF(@s,Number,0,',')

FROM master.dbo.spt_values

WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'

ORDER BY Number DESC

RETURN @s

END

GO

DECLARE @s VARCHAR(100)

SET @s='W'

SELECT @s,dbo.InsertCommas(@s)

SET @s='ABWI'

SELECT @s,dbo.InsertCommas(@s)

SET @s='AW'

SELECT @s,dbo.InsertCommas(@s)

Something like this?

DECLARE @x XML

SET @x='

<xml xml:lang="en">

<Sect itemtoshow="6" name="Automobile" pg="3328">

.

.

</Sect>

</xml>

'

SELECT r.value('.','VARCHAR(30)') AS Tag,

r.value('../../@name','VARCHAR(30)') AS ParentTag

FROM @x.nodes('//@name') AS x(r)

Try this

WITH CTE AS (

SELECT RowID,

DateID,

Birthdate,

FirstName,

...

WITH CTE AS (

SELECT Sequence,ProductID,Colour,

ROW_NUMBER() OVER(PARTITION BY Colour ORDER BY Sequence) AS rn

FROM dbo.TmpOrder),

Results AS (

SELECT MIN(Sequence) AS StartingSequence,

...

Thanks!

Try this

SELECT DATEDIFF(Year,(@BirthDate,@TargetDate) - CASE WHEN DATEADD(Year, DATEDIFF (Year, (@BirthDate, @TargetDate), (@BirthDate) > @TargetDate THEN 1 ELSE 0 END

You may want to vote for this

http://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions

Look at the OUTPUT clause for INSERT statements in BOL

-- SQL Server 2005/2008 only

SELECT rowid,

orderid,

CAST(cost AS DECIMAL(10,4)) / CAST(COUNT(*) OVER(PARTITION BY orderid) AS DECIMAL(10,4)) AS...

Might be of interest

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=318084

WITH CTE AS (

SELECT I,VALUE,

ROW_NUMBER() OVER(ORDER BY I)-

ROW_NUMBER() OVER(PARTITION BY VALUE ORDER BY I) AS rnDiff

FROM...