SQL: Cumulative Sums by Month

I am working with Netezza SQL.

I have the following table (my_table):

   type var1 var2     date_1  date_2
     a    5    0 2010-01-01 2009-2010
     a   10    1 2010-01-15 2009-2010
     a    1    0 2010-01-29 2009-2010
     a    5    0 2010-05-15 2010-2011
     a   10    1 2010-05-25 2010-2011
     b    2    0 2011-01-01 2010-2011
     b    4    0 2011-01-15 2010-2011
     b    6    1 2011-01-29 2010-2011
     b    1    1 2011-05-15 2011-2012
     b    5    0 2011-05-15 2011-2012

Here, date_2 is the "april to april" year of date_1. E.g. date_1 = 2010-01-01 falls between April 1st 2009 and April 1st 2010, therefore date_2 = 2009-2010.

My Question:

For each unique value of "type" in each unique value of date_2: I want to take a monthly cumulative sum of var1 and var2 ... such that the cumulative sum restarts for the next unique value of date_2.
Note here that the first month is always April (not January).

The final result should look something like this:

  type month_position    date_2 cumsum_var1 cumsum_var2
1    a             10 2009-2010          16           1
2    a              2 2010-2011          15           1
3    b             10 2009-2010          12           1
4    b              2 2010-2011           6           1

I think I figured out how to do a cumulative sum:

select (var1) over (partition by type, date_2 order by date_1 rows between unbounded preceding and current row) as cumulative_var1;

I know the "month_position" variable can be done using a CASE WHEN statement:

select case 
when month(date_1) = 4 then 1,
when month(date_1) = 5 then 2,
when month(date_1) = 6 then 3,
....
when month(date_1) = 3 then 12
end as month_position;

But I am not sure how to assemble the final query.

Can someone please show me how to do this?

Thanks!

Solution

What about:

SELECT type,
       month_position,
       date_2,
       SUM(var1) OVER (PARTITION BY type, date_2 ORDER BY month_position) as cumsum_var1,
       SUM(var2) OVER (PARTITION BY type, date_2 ORDER BY month_position) as cumsum_var2
FROM (
    SELECT type,
           SUM(var1) AS var1,
           SUM(var2) AS var2,
           1 + MOD(8 + month(date_1),12) as month_position,
           date_2
     FROM my_table
     GROUP BY type, MOD(8 + month(date_1),12), date_2
) T

The MOD(8 + month(date_1),12) is a short version of the SELECT CASE of your question where most of the work is done by a modulo (returned values go from 0 for April to 11 for March). Making it short makes it easier to use.