• Phil Parkin (7/31/2015)


    sgmunson (7/31/2015)


    Naina_11 (7/27/2015)


    it's done. I added a Level column with 0 and 1 and ordered accordingly.

    Thanks

    Another alternative:

    DECLARE @testing AS TABLE (

    DepDate datetime,

    val int

    );

    INSERT INTO @testing VALUES ('2014-01-10 00:00:00.000', 1);

    INSERT INTO @testing VALUES ('2014-05-19 00:00:00.000', 10);

    INSERT INTO @testing VALUES ('2014-08-15 00:00:00.000', 20);

    INSERT INTO @testing VALUES ('2014-11-20 00:00:00.000', 30);

    WITH MONTHS AS (

    SELECT 1 AS MTH, DATEADD(month, 1, '2013-12-01') AS MONTH_START, DATEPART(quarter, DATEADD(month, 1, '2013-12-01')) AS [QUARTER]

    UNION ALL

    SELECT M.MTH + 1, DATEADD(month, 1, M.MONTH_START), DATEPART(quarter, DATEADD(month, 1, M.MONTH_START))

    FROM MONTHS AS M

    WHERE M.MTH + 1 < 13

    ),

    QUARTERS AS (

    SELECT 1 AS QTR, 'st' AS EXT

    UNION ALL

    SELECT 2, 'nd'

    UNION ALL

    SELECT 3, 'rd'

    UNION ALL

    SELECT 4, 'th'

    )

    SELECT ISNULL(CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', 'GRAND') AS [QUARTER],

    ISNULL(CAST(LEFT(DATENAME(month, M.MONTH_START), 3) AS varchar(5)), 'TOTAL') AS [MONTH],

    SUM(T.val) AS VAL_TOTAL

    FROM MONTHS AS M

    INNER JOIN QUARTERS AS Q

    ON M.[QUARTER] = Q.QTR

    LEFT OUTER JOIN @testing AS T

    ON M.MONTH_START = DATEADD(day, 1 - DATEPART(day, T.DepDate), T.DepDate)

    GROUP BY CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', M.[MONTH_START]

    WITH ROLLUP

    ORDER BY [QUARTER], [MONTH]

    Phases of the moon missing?? :w00t:

    Phil, you mean:

    DATEPART(MoonPhase, T.DepDate) :w00t:



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]