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:
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]