There was a question on the forums the other day that asked how to get a total for the current month, the current quarter, and year to date.  It is actually a pretty easy task when using a calendar table.  I have one you can use here (I fixed an error in it that I just found too!): http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

For an example, I will use the SalesOrderHeader and SalesOrderDetail tables from AdventureWorks, and use the LineTotal to do the summations.  I will group on the AccountNumber.   So the query is:

select SalesOrderHeader.AccountNumber,
        sum(case when today.year = calendar.year  and today.month >= calendar.month
                              then LineTotal else 0 end ) as ytdTotal,
        sum(case when today.year = calendar.year  and today.quarter = calendar.quarter
                              then LineTotal else 0 end ) as quarterTotal,
        sum(case when today.year = calendar.year  and today.month = calendar.month 
                              then LineTotal else 0 end ) as monthTotal
from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar 
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today 
            on today.dateValue = '2001-12-31' --vary this value to vary the date of "today"
group by SalesOrderHeader.AccountNumber

So let's briefly build this query from the ground up.  First, the base data from the sales schema in adventureworks.

from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId

Next, join in the calendar table two times:

from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar 
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today 
            on today.dateValue = '2001-12-31'

The first calendar will associate one row in the calendar table with each row being output.  The cardinality of the set will not be changed because the calendar has one row per day.  The second copy of the calendar table I aliased today because it will usually represent the current day (it could be anything).  What this does is add another copy of the calendar table, giving you all of the columns of that date to do math with (if you don't see that, it will be more clear in a moment (I hope :)

Next comes the column that will be grouped by.  In this case I am using the account number. 

select SalesOrderHeader.AccountNumber
from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar 
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today 
            on today.dateValue = '2001-12-31'
group by SalesOrderHeader.AccountNumber

This makes the set now one row per account number, with a "table" of values associated to each account number that we can use aggregates on.  This is where the interesting stuff begins (you were hoping, weren't you!):

select SalesOrderHeader.AccountNumber,
        sum(case when today.year = calendar.year  and today.month >= calendar.month
                              then LineTotal else 0 end ) as ytdTotal,
        sum(case when today.year = calendar.year  and today.quarter = calendar.quarter
                              then LineTotal else 0 end ) as quarterTotal,
        sum(case when today.year = calendar.year  and today.month = calendar.month 
                              then LineTotal else 0 end ) as monthTotal
from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar 
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today 
            on today.dateValue = '2001-12-31'
group by SalesOrderHeader.AccountNumber

Because each one of these tables for aggregates has calendar table rows associated with the date or order, and the "current" date, you can use this fact to pick out values you are interested in for a given sum.  take the ytdTotal one:

        sum(case when today.year = calendar.year  and today.month >= calendar.month
                              then LineTotal else 0 end ) as ytdTotal,

For each row I check the year of the order, and see if it matches the year we are looking for.  Then check to see if the order month is less than or equal to the current month.  If it is, put out the LineTotal, else just use 0.  You can build very powerful expressions that are extremely fast by varying the way you compare the current day to the date of the order.  In an earlier article, I also showed how you could use relative positions in the calendar table to get the past 6 months, past 13 months, etc, without any kind of ugly math. For this example, if you wanted to get the past 6 months, you could simply add:

sum(case when today.relativeMonthCount -5 <= calendar.relativeMonthCount 
                       and today.relativeMonthCount >= calendar.relativeMonthCount
        then LineTotal else 0 end ) as past6MonthsTotal

Getting the equation just right can be a bit tricky (at least for my brain :), so I generally test my date ranges using a query like this:

select distinct calendar.year, calendar.month
from dbo.calendar
          join dbo.calendar as today
               on today.dateValue = '2001-12-31'
where today.relativeMonthCount -5 <= calendar.relativeMonthCount
    and today.relativeMonthCount >= calendar.relativeMonthCount

Which returns:

year     month
-------- --------
2001    7
2001    8
2001    9
2001    10
2001    11
2001    12

Crossposted to http://drsql.spaces.live.com