Labels

Saturday, December 31, 2011

How to calculate Total Due on Quarter Basis for Every Year ?

SELECT YEAR = YEAR(OrderDate),                      COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ,OrderDate) = 1 THEN TotalDue
                                    END),1),'') AS 'Q1',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 2 THEN TotalDue
                                    END),1),'') AS 'Q2',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 3 THEN TotalDue
                                    END),1),'') AS 'Q3',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 4 THEN TotalDue
                                    END),1),'') AS 'Q4'
FROM  AdventureWorks2008.Sales.SalesOrderHeader soh
WHERE YEAR(OrderDate) > 2001
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)

/* YEAR     Q1          Q2                Q3                Q4
2002  6,678,449.12      7,430,122.29      12,179,372.04     9,798,486.39
2003  7,738,309.35      9,727,845.55      16,488,806.73     15,192,201.07
2004  12,824,418.47     16,262,217.91     50,840.63   */

No comments:

Post a Comment