Try this

DECLARE @DetailTable TABLE(Store INT, PhoneNr VARCHAR(30))

INSERT INTO @DetailTable(Store, PhoneNr)

SELECT 4711, '49911-1111111' UNION ALL

SELECT 4711, '49911-1111112' UNION ALL

SELECT 4711, '49911-1111113';

WITH CTE AS (

SELECT Store, PhoneNr,

...

Jeff Moden (3/27/2012)

---

Eugene Elutin (3/27/2012)

---

My query will only ...

Eugene Elutin (3/27/2012)

---

I still couldn't get it from your explanation, but looks like I do see now what you want ...

Lets create some more test data (around 1.3 millions records)

declare...

The "Group Islands of Contiguous Dates" article I referenced explains how this works.

See the link here

http://www.sqlservercentral.com/articles/T-SQL/71550/

WITH CTE AS (

SELECT [Month],[ItemID],[count A],[count B],

ROW_NUMBER() OVER(PARTITION BY [ItemID] ORDER BY [Month]) -

...

Use OUTER APPLY

SELECT x.name.value('@name[1]', 'VARCHAR(10)') AS name

,y.name.value('@ID[1]', 'INT') as ID

,y.name.value('@color[1]', 'VARCHAR(10)') as Color1

,z.name.value('@color[1]', 'VARCHAR(10)') as Color2

FROM @data.nodes('/Automobile/Car') x(name)

OUTER APPLY x.name.nodes('Properties') y(name)

OUTER APPLY x.name.nodes('PropertiesGroup') z(name)

GilaMonster (3/26/2012)

---

Nothing wrong with a guid primary key. What causes performance problems is a Guid column used as the clustered index key. So if you're going to have Guid primary...

Just a GROUP BY should do it

SELECT [Model Number], MIN(Price) AS Price

FROM myTable

GROUP BY [Model Number]

SELECT u.C8,

(SELECT g.groupname+';' AS "text()"

FROM group_cache g

WHERE...

This gives you just the active and idle time in minutes. You'll have to format this to give HH:MM:SS.

WITH AllTimes AS (

SELECT MachineID, TimeStart AS tm

FROM @TempSource

UNION

...

See if this helps

DECLARE @phoneDetail TABLE(CustomerID INT,Row INT,PhoneType VARCHAR(20),

...

Still not clear what you're asking. If your XML is in a table, you can use CROSS APPLY to get the values.

SELECT x.r.value('@date_changed','DATETIME') AS date_changed,

...

This will give the XML as columns. You'll need to give more information about how to calculate the other columns you're after.

DECLARE @x XML

SET @x='<Rates>

<Rate date_changed="Nov 10 2011...

Using recursive CTEs

WITH Recur AS (

SELECT MessageThread,MessageContent,MessageID,MessageDate

FROM ThreadMessage

WHERE MessageParent=0

UNION ALL

SELECT r.MessageThread,r.MessageContent,m.MessageID,m.MessageDate

FROM ThreadMessage m

INNER JOIN Recur r ON r.MessageID=m.MessageParent

)

SELECT MessageThread AS ThreadID,

...

You can use recursive CTEs for this

SET NOCOUNT ON

DECLARE @fctTable TABLE(Id INT,refTableId INT)

INSERT INTO @fctTable(Id,refTableId)

SELECT 1 , 2 UNION ALL

SELECT 2 , 7 UNION ALL

SELECT 3 , 4 UNION ALL

SELECT...