Try this for a size
😎
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)) 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;
Results
ID number DateOfArrival DateOfDeparture JAN14 FEB14 MAR14 APR14 MAY14 JUN14 JUL14 AUG14 SEP14 OCT14 NOV14 DEC14
----------- ------------- --------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1056 2014-04-29 2014-05-03 0 0 0 1 3 0 0 0 0 0 0 0
2031 2014-03-20 2014-03-26 0 0 6 0 0 0 0 0 0 0 0 0
3245 2014-06-19 2014-06-20 0 0 0 0 0 1 0 0 0 0 0 0