I believe that you should be using the RANK function rather than ROW_NUMBER, otherwise participants with the same end time may be unfairly awarded a different number of points. In...

Sorry! - that'll teach me to submit an answer without testing it.

However, a minor change fixes the problem I think.

WHERE (a.START_DATE = DATEADD(day, -14, GETDATE()))

Here's a variation that removes the...

If you define the WHERE clause like this:

WHERE (a.START_DATE >= DATEADD(day, -14, GETDATE()))

AND (a.END_DATE <= DATEADD(day, 14, GETDATE()))

you should get performance improvement as the DATEADD expressions will...

I haven't quite reproduced your required results, but then I don't believe that those results are really what you want!

Here is the TSQL to set up some test data....

Try this FOR XML EXPLICIT query.

SELECT 1 AS Tag,

0 AS Parent,

NULL AS [REQUISITIONS!1],

NULL AS [RECORD!2!EMPLOYEEID!element],

NULL AS [RECORD!2!FIRSTNAME!element],

...

Agreed.

I've created a #Sales table and populated it with some test data - providing a TSQL script to generate the test data is the preferred method of presenting test data...

3 questions:

Q1. Is it possible that for a given row the height column might be vaued but the weight column may be null, or vice versa? Or are the height...

REVERSE is one of the less performant string functions in TSQL, so this alternative expression calls REVERSE only once.

SELECT RIGHT(@nam, CHARINDEX(' ', REVERSE(@nam)) - 1)

The expression above and also some...

You might wish to cast to a float or decimal value before calculating the average.

;WITH AccItem AS (

SELECT itemId,

ROW_NUMBER() OVER (ORDER...

I think you want the average number of rows between consecutive rows with the same itemId with the row order defined by the date column, rather than the average number...

It is possible to convert an YYYYMMDD integer representation of a date directly to a datetime value as follows:

SELECT DATEADD(year, run_date / 10000 - 1900,

DATEADD(month, (run_date...

I'm guessing that the required start date is for the current year and that the varchar column that stores the month and day is consistently in the format M/D where...

Lookup DATEDIFF TSQL function in SQL Server Books Online

I'm not entirely sure what you mean, but assuming you have a single string that comprises 2 sequences of characters that do not include any space characters separated by a...