WITH LastChildren AS (

SELECT d.ChildID

FROM #tree d

WHERE NOT EXISTS(SELECT * FROM #tree d2 WHERE d2.ParentID = d.ChildID)

),

Recur AS (

SELECT ChildID,ParentID, CAST('/' + CAST(ChildID AS VARCHAR(10)) AS VARCHAR(1000)) AS Path, 1...

See if this helps

CREATE TABLE #Folderlist(id INT, folder_name VARCHAR(10),parent_id INT)

INSERT INTO #Folderlist(id ,folder_name, parent_id)

SELECT 1, 'c', 101 UNION ALL

SELECT 2,'b',202 UNION ALL

SELECT 3,'c',203;

CREATE TABLE #Teamlist(team_id INT, Team_name VARCHAR(10),Parent_folderid INT)

INSERT INTO...

Try this

SELECT COALESCE(x.r.value('(DisplayName/text())[1]','VARCHAR(100)'),PARSENAME(x.r.value('local-name(.)','VARCHAR(20)'),1)) + ':' + COALESCE(x.r.value('(OldValue/text())[1]','VARCHAR(100)'),'NULL') AS [OLD VALUE],

COALESCE(x.r.value('(DisplayName/text())[1]','VARCHAR(100)'),PARSENAME(x.r.value('local-name(.)','VARCHAR(20)'),1)) + ':' + COALESCE(x.r.value('(NewValue/text())[1]','VARCHAR(100)'),'NULL') AS [NEW VALUE]

FROM @p.nodes('/root/*') AS x(r)

Bit late to the party but you can convert this to a nested set representation using the code below

See Jeff Moden articles here[/url] for info on nested sets

WITH Source AS...

You can use LAG

SELECT HolidayDate, HolidayName,

LAG (HolidayDate) OVER (PARTITION BY HolidayName ORDER BY HolidayDate ) AS PreviousHolidayDate

FROM @table

ORDER BY HolidayDate;

SELECT DISTINCT [AlphaExtension],

STUFF((SELECT A.[NoteText] + '< BR />' AS "text()"

FROM #temp A

WHERE A.[AlphaExtension]=B.[AlphaExtension]

FOR XML PATH(''),TYPE).value('./text()[1]','NVARCHAR(MAX)'),1,1,'') As [NoteText]

FROM #temp B

GROUP BY [AlphaExtension], [NoteText]

You're welcome.

Change

'0x' + SUBSTRING(RTRIM([program_name]), 32, 32)

to

CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)

order by max(datepart(month,CheckDate))

CREATE TABLE #Temp([Group] CHAR(1),Name CHAR(1),Value INT)

INSERT INTO #Temp([Group],Name,Value)

SELECT 'p','a',1 UNION ALL

SELECT 'p','b',2 UNION ALL

SELECT 'p','c',3 UNION ALL

SELECT 'p','d',4 UNION ALL

SELECT 'q','a',5 UNION ALL

SELECT 'q','b',6 UNION ALL

SELECT 'q','d',7 UNION ALL

SELECT 'r','a',8...

Try using CROSS APPLY instead of UNPIVOT

SELECT name,Hours,details FROM #Temp

CROSS APPLY

(

VALUES

([Week_1],'Week_1'),

([Week_2],'Week_2'),

([Week_3],'Week_3'),

([Week_4],'Week_4'),

([Week_5],'Week_5'),

([Week_6],'Week_6'),

([Week_7],'Week_7'),

([Week_8],'Week_8'),

([Week_9],'Week_9'),

([Week_10],'Week_10')

) c(Hours,details)

select paymentMethod,

COUNT(CASE WHEN daerahKutipan = 1 THEN payer END) figure_Seremban,

COUNT(CASE WHEN daerahKutipan = 3 THEN payer END) figure_KualaPilah,

COUNT(CASE WHEN daerahKutipan = 4 THEN payer END) figure_PortDickson,

COUNT(CASE WHEN daerahKutipan = 5...

SELECT x5.r5.value('.','VARCHAR(10)'),

x4.r4.value('@id','INT'),

x3.r3.value('@id','INT'),

x2.r2.value('@id','INT')

FROM @MyXML.nodes('/RS') AS x1(r1)

CROSS APPLY x1.r1.nodes('R') AS x2(r2)

CROSS...

Not quite a complete solution, it doesn't handle spanning midnight

WITH Hours(hrStart,hrEnd) AS (

SELECT CAST(hrStart AS TIME),CAST(hrEnd AS TIME)

FROM (

VALUES ('00:00','01:00'),('01:00','02:00'),('02:00','03:00'),('03:00','04:00'),

...

Interesting article!

Paul White posted some useful information about MERGE performance

http://www.sqlservercentral.com/Forums/FindPost1466528.aspx