Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Query to split difference (number of nights) between 2 dates into respective "Month" column RE: Query to split difference (number of nights) between 2 dates into respective "Month" column

  • 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