The change is simple, the sequence needs to be shifted so it starts counting at zero instead of one as otherwise it counts the night when it ends not when it starts. Here is the correction, changes in bold.
😎
USE tempdb;
GO
SET NOCOUNT ON;
;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)) - 1 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;