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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

You can use LAG

SELECT HolidayDate, HolidayName,

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

FROM @table

ORDER BY HolidayDate;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

You're welcome.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Change

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

to

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

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

order by max(datepart(month,CheckDate))

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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'),

...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Interesting article!

Paul White posted some useful information about MERGE performance

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

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537