How about this:

;WITH cteWithId(id, fullname, phone) AS (

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

FullName,

...

I presume you are storing the time-only value as a datetime column in the destination table rather than a character string. If so, removing the milliseconds (not microseconds!) component is...

The DATEADD / DATEDIFF expression below is one efficient method to remove the date component from a datetime value, that is, set the date component value to zero (1900-01-01).

SELECT DATEADD(day,...

It's the modulo operator - the remainder after integer division - look it up in Books Online.

I assume it's being used here to execute the update query on 20% of...

Here's another way to do it in SQL Server 2000, that uses a similar concept as the ROW_NUMBER() function method that can be used in SQL Server 2005. This query...

Out of interest, an alternative and probably better solution on SQL Server 2005 is to use the ROW_NUMBER() function to help identify consecutive sequences of unfilled slots. Please note that...

I'm not 100% confident that I understand what you are trying to do, but I think that you are looking for the shortest consecutive sequence of unfilled slots where the...

A recursive CTE is not necessary for this -- a Tally (Numbers) table can do the job more efficiently.

The following uses the built-in numbers table: master.dbo.spt_values, but if you need...

Your problem is easier if you normalise your data as follows:

CREATE TABLE #Location (

Row char(1) NOT NULL,

Col tinyint NOT NULL,

Filled bit...

I''m digressing from the OP's date format conversion question,...

This query also returns your expected results I think:

SELECT A.CountryName AS CountryName1, B.CountryName AS CountryName2

FROM Countries A INNER JOIN Countries B

ON (A.GroupId = B.GroupId AND A.CountryName...

This SQL reproduces your expected results on a SQL 2000 server.

SELECT

A.PatientId AS PatientID,

A.ArriveTime AS ArriveTime,

MIN(E.ExitTime) AS ExitTime,

...

If you must produce formatted output directly from SQL rather than in the front end then your approach is reasonable, however, there are some unecessary modulo operations in your expressions,...

I'm confused by the first bit of the CTE... what does the following do?

ROW_NUMBER() OVER (ORDER BY ref)

- ROW_NUMBER() OVER (PARTITION BY (CASE WHEN StartStop = 'Stop' THEN 1 ELSE...

WITH StartStop AS (

SELECT Ref, IsNull(StartStop, 'Start') AS StartStop, theDate

FROM #TempTable

)

SELECT a.Ref, a.theDate AS Start, Min(b.theDate) AS Stop, DateDiff(dd, a.theDate, Min(b.theDate)) AS...