Sorry, have to rant, and this is nothing to do with what Eirikur's code does, just the fact that the CTE is started with an unnecessary semicolon. It is something I see many people doing in their code. This is why I totally dislike the sample code in BOL regarding CTE's. A semicolon is NOT required at the beginning of a CTE (WITH clause). Semicolons are statement terminators, not statement begininators. Looking at the code Eirikur wrote take a close look at the CTE:

USE tempdb;

GO

SET NOCOUNT ON; -- <<Here, we have a semicolon properly used as a statement terminator

-- Followed directly below by another semicolon that is not needed because of the terminator above.

;WITH SAMPLE_DATA ([ID number],DateOfArrival,DateOfDeparture)

AS (SELECT * FROM (VALUES

(2031,'2014-03-20','2014-03-26')

,(1056,'2014-04-29','2014-05-03')

,(3245,'2014-06-19','2014-06-20')

) AS X([ID number],DateOfArrival,DateOfDeparture)

)

,MONTH_NUMBERS AS (SELECT MNO FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS X(MNO))

,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),

(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,COUNTING_DAYS AS

(

SELECT

SD.[ID number]

,SD.DateOfArrival

,SD.DateOfDeparture

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival)) AS MONTH_NO

,COUNT(SD.[ID number]) OVER

(

PARTITION BY SD.[ID number]

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival))

) AS COUNT_IN_MONTH

FROM SAMPLE_DATA SD

OUTER APPLY (SELECT TOP(DATEDIFF(DAY,SD.DateOfArrival,SD.DateOfDeparture) ) ROW_NUMBER()

OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM

)

SELECT

CD.[ID number]

,CD.DateOfArrival

,CD.DateOfDeparture

,MAX(CASE WHEN CD.MONTH_NO = 1 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JAN14

,MAX(CASE WHEN CD.MONTH_NO = 2 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS FEB14

,MAX(CASE WHEN CD.MONTH_NO = 3 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS MAR14

,MAX(CASE WHEN CD.MONTH_NO = 4 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS APR14

,MAX(CASE WHEN CD.MONTH_NO = 5 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS MAY14

,MAX(CASE WHEN CD.MONTH_NO = 6 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JUN14

,MAX(CASE WHEN CD.MONTH_NO = 7 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JUL14

,MAX(CASE WHEN CD.MONTH_NO = 8 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS AUG14

,MAX(CASE WHEN CD.MONTH_NO = 9 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS SEP14

,MAX(CASE WHEN CD.MONTH_NO = 10 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS OCT14

,MAX(CASE WHEN CD.MONTH_NO = 11 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS NOV14

,MAX(CASE WHEN CD.MONTH_NO = 12 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS DEC14

FROM COUNTING_DAYS CD

GROUP BY CD.[ID number]

,CD.DateOfArrival

,CD.DateOfDeparture; -- << Not only that, but the code using the CTE is also terminated by a semicolon.

If you read BOL and the information regarding CTEs, it clearly states that for a CTE the PREVIOUS statement must be terminated by a semicolon. The practice of starting a CTE with a semicolon to ensure that the previous statement is terminated by a semicolon, imho, teaches the wrong way to code.

.It is like writing sentences like this .Putting the punction at the start of each sentence to ensure that the previous sentence is properly terminated

.Does this actually make any sense ?Personally, it doesn't.