Query to split difference (number of nights) between 2 dates into respective "Month" column

• Eirikur Eiriksson

  SSC Guru

  Points: 182918

  More actions

  September 27, 2014 at 5:15 pm

  #1748645

  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;