Hi Jeff,

This should give you what you want

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)

SELECT xt.RowNum
   ,wszDb = StatsUsed.XMLCol.value('(../p:Field[@FieldName="wszDb"])[1]/@FieldValue','NVARCHAR(128)')
   ,wszSchema...

SQL Server 2012 has built in support for this using the OFFSET ... FETCH NEXT syntax

https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

Try this

WITH Src AS (
SELECT RuleID,
   DateTime1,
     DName,
     CASE WHEN LAG(RuleID) OVER(PARTITION BY DName ORDER BY DateTime1) <> 'DBRE' THEN 0 ELSE 1 END...

Change
cross apply @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp/CCycles/Cycle') as p(cy)
to
cross apply g.c.nodes('CCycles/Cycle') as p(cy)

Possible workaround using NULLIF

select CASE

   WHEN CHARINDEX('-','LOHAUANIA') <> 0

     THEN substring(CAST('LOHAUANIA' AS VARCHAR(20)),1,CAST(NULLIF(CHARINDEX('-','LOHAUANIA'),0) AS VARCHAR(20)) -1)

         ELSE 'AB'

         END

SELECT x.r.value('@code[1]','CHAR(1)') AS Code,
   x.r.value('@description[1]','VARCHAR(20)') AS Description,
   x.r.value('@validFrom[1]','DATE') AS validFrom
FROM DES_Nomenclator
CROSS APPLY Info_untyped.nodes('/codeSystem/values/value') AS x(r);

Use a cte

with cte as (
select top 3 q.ID,TimeDate,q.Stockcode,FG_3_55g,FG_3_15g,FG_2_5g,FG_2g,FG_1_6g,FG_1g,[FG_710/500],FG_bp
,FG_3_55g + FG_3_15g + FG_2_5g + FG_2g + FG_1_6g + FG_1g + [FG_710/500]+ FG_bp as TotalFG
from...

This is using a solution by Itzik Ben-Gan

WITH C1 AS (
SELECT id, EffectiveDate, Enddate,
   CASE WHEN EffectiveDate <= MAX(Enddate) OVER(ORDER BY EffectiveDate, Enddate ROWS BETWEEN UNBOUNDED PRECEDING...

See if this works for you

WITH CTE1 AS (
SELECT Userid,EventTypeName,EventStartTime,RN,
  CASE WHEN LAG(EventTypeName) OVER(PARTITION BY Userid ORDER BY RN) IN ('EsxEventTypeAppWentToBackground','EsxEventTypeAppTerminated','EsxEventTypeLogout') THEN 1 ELSE 0...

Try joining to a calendar table

http://www.sqlservercentral.com/articles/calendar/145206/

You can use


DENSE_RANK() OVER(PARTITION BY ColumnA ORDER BY ColumnB) +
DENSE_RANK() OVER(PARTITION BY ColumnA ORDER BY ColumnB DESC) - 1

instead of 

COUNT(DISTINCT ColumnB)...

Something like this?

-- Fixed length, 4 in this case

WITH Digits(N) AS (
SELECT N
FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) D(N)
)
SELECT d1.N+d2.N+d3.N+d4.N AS N
FROM Digits d1