Geek Answers Handbook

Sum the field for each month cummulatively and dynamically

I have a table that contains about 50,000 records of information that has been set up to look back from the current fiscal year.

In its current form, I have not updated this table since last month, so the data there currently suggests that we are still looking back on April 1, 2011.

note (when I update the data, there will only be data for April 2012, since we are in April now, then in May it will have April 2012 and May 2012, etc.)

Each entry has 4 columns that interest me:

Department,
Incident date,
month,
year,
reduced

Both columns of the month and year were generated from the date of the incident field , which is in this format:

2011-06-29 00:00:00.000

, , .

, 2011 /, , .

, , , , ...

, , reimport , , ()

+5
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
+1

, Department, Year Month. , WHERE.

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
+1

More articles:


All Articles