Smash125 (10/12/2012)
SELECTYEAR(OrderDate) ASOrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC
Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL
output should like this
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL
This sort of formatting should be performed in the presentation layer, not the database layer.
If you insist on doing it in the database layer, it can be achieved like this: -
SELECT OrderYear,
CONVERT(VARCHAR(100), CAST(Q1 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q2 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q3 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q4 AS money), 1)
FROM (SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q4
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) a
ORDER BY OrderYear;