Sum a field for each month cummulatively and dynamically

Question

I have a table which contains around 50,000 records of information which has been set up to look back as far as start of current financial year.

As it stands I have not updated this table since last month so currently the data in there assumes we are still looking back as far back as April 1st 2011.

note(when i refresh the data, there will only be April 2012's data in there as we are now in April, then in May it will have April 2012 and May 2012 and so on...)

Each record has 4 columns I am concerned with:

Department,
Incident date,
month,
year,
reduced

Both the month and year columns have been generated from the incident date field which is in this format:

2011-06-29 00:00:00.000

I need to for each department, sum the reduced but in a cumulative fashion.

eg seen as though April 2011 will be the earliest month/year data I have at the moment, I will want to know the total reduced for every department for April.

Then for May I want April & May combined, for June I need April,May,June and so on...

Is there an intelligent way to do this so that as soon as I reimport data into this table it will realise that there is now only one month and that the year has updated and will for now until next month only display April's sum(reduced)

Answer 1

The following will return the cumulative totals grouped by Department, Year and Month. If you're clearing out the data from the previous tax year when refreshing then you can omit the WHERE clause.

SELECT      T1.[Year],
            T1.[Month],
            T1.Department,
            SUM(T2.Reduced) ReducedTotals
FROM        [TABLENAME] T1
INNER JOIN  [TABLENAME] T2 ON ( T1.Department = T2.Department AND T1.IncidentDate >= T2.IncidentDate )
WHERE       T1.IncidentDate >= '2012-04-01'
GROUP BY    T1.[Year],
            T1.[Month],
            T1.Department
ORDER BY    T1.[Year],
            T1.[Month],
            T1.Department

Answer 2

select t1.id, t1.singlenum, SUM(t2.singlenum) as sum 

    from @t t1 inner join @t t2 on t1.id >= t2.id
    group by t1.id, t1.singlenum
    order by t1.id