SQL Pivot for multiple rows...CONFUSED?

Okay, I have not been doing SQL for a while. This is the most in depth I have had to get. Basically I have a to pivot a table and then break it up depending on the dates.

Right now I have a transaction that goes through. There are 3 transaction types. Lets say Delivered, Shipped, Returned. So my table has a Transaction ID, Transaction Date, and Transaction type. I need to pivot this and count then sum the transaction types. I have this code so far:

SELECT
    SUM(Delivered),
    SUM(Shipped),
    SUM(Returned),
    TransactionID,
    TransactionDate
FROM
    (
        (SELECT
            TransactionID,
            TransactionDate,
            TransactionType
        FROM Transactions) AS Ttable
        PIVOT
        (
            COUNT(TransactionType)
            FOR TransactionTYpe IN
            (
                [Delivered],
                [Shipped],
                [Returned]
            )
         ) as Pvt1
    )

This gets the sum of the count for each transaction type. However, This gives me the totals for ALL of the transaction types that exsist. I need to get all of this for a block of time. For example (and I know this i开发者_如何学运维s going to be a lot of variables but its the only way my app can do it), I need to find out the sum of all the transaction types for January 2010 - December 2012 for each month. So 12x3. How can I break this up like that? Please help! Going crazy!

Here's a starting point:

SELECT TransactionMonth, [Delivered], [Shipped], [Returned] 
 from (select
          TransactionType
         ,cast(datename(mm, TransactionDate) + ' 1,  ' + datename(yy, TransactionDate) as smalldatetime) TransactionMonth
        from Transactions) ByMonth
 pivot (count(ByMonth.TransactionType)
        for ByMonth.TransactionType in ([Delivered],[Shipped],[Returned] )
        ) xx
 order by TransactionMonth

It wasn't entirely clear what the source data looked like or what your final grid should be, so I made some assumptions:

• I assume there is one transaction on one date for each TransactionId. I factor it out of the final query, and generate totals by calendar month
• I return results for every entry in the table. Add a where clause with appropriate date-filtering parameters to the SELECT clause to get less.
• There's got to be a better way to "cleanse" a datetime into a "year-month" form, use it if you find it
• I tested this on a simlar table of my own, then converted to your names -- pretty sure I got it all right, but some typos may have slipped in. (I was using SQL 2005.)