View Single Post
  #3 (permalink)  
Old October 17th, 2006, 12:33 AM
Jeff Moden Jeff Moden is offline
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

You can do some things with "GROUPING" (not included below) to make the subtotals and totals more apparent but, considering you posted no schema nor data, this should get you started once you make the appropriate substitutions for table and column names as required...

 SELECT d.Company,
        d.[Year],
        SUM(CASE WHEN [Month] = 01 THEN Transactions ELSE 0 END) AS Jan,
        SUM(CASE WHEN [Month] = 02 THEN Transactions ELSE 0 END) AS Feb,
        SUM(CASE WHEN [Month] = 03 THEN Transactions ELSE 0 END) AS Mar,
        SUM(CASE WHEN [Month] = 04 THEN Transactions ELSE 0 END) AS Apr,
        SUM(CASE WHEN [Month] = 05 THEN Transactions ELSE 0 END) AS May,
        SUM(CASE WHEN [Month] = 06 THEN Transactions ELSE 0 END) AS Jun,
        SUM(CASE WHEN [Month] = 07 THEN Transactions ELSE 0 END) AS Jul,
        SUM(CASE WHEN [Month] = 08 THEN Transactions ELSE 0 END) AS Aug,
        SUM(CASE WHEN [Month] = 09 THEN Transactions ELSE 0 END) AS Sep,
        SUM(CASE WHEN [Month] = 10 THEN Transactions ELSE 0 END) AS Oct,
        SUM(CASE WHEN [Month] = 11 THEN Transactions ELSE 0 END) AS Nov,
        SUM(CASE WHEN [Month] = 12 THEN Transactions ELSE 0 END) AS [Dec],
        SUM(Transactions) AS Total,
        SUM(Transactions)/24 AS Average
   FROM (--Derived table "d" finds count for year and month
         SELECT Company,
                [Year] = DATEPART(yy,TransactionDate),
                [Month] = DATEPART(mm,TransactionDate)
                Transactions = COUNT(*)
           FROM yourtable
          GROUP BY Company,
                   DATEPART(yy,TransactionDate),
                   DATEPART(mm,TransactionDate)
        ) d
  GROUP BY d.Company,
           d.[Year]
   WITH ROLLUP
  ORDER BY d.Company,
           d.[Year]

...and, in a properly indexed environment, will process millions of rows in scant seconds (ie. 4 million rows in about 5-7 seconds).

--Jeff Moden
Reply With Quote
The Following User Says Thank You to Jeff Moden For This Useful Post:
moorecreative (April 12th, 2015)