sql server - Write Sql Query -

we have table 3 columns (name_of_office, month, amount), below:

name_of_office      month     amount ------------------------------------ divisionbhopal      04         125 divisionbhopal      05          50 divisionbhopal      06         100 divisionbhopal      10         125 divisionsagar       04         600 divisionsagar       05         520 divisionsagar       06         400 divisionsagar       10         100 

financial year month should used calculation.

by formula start sum april selected month.

suppose user select month of june (from dropdown)

calculation should performed this

name_of_office     sum upto previous month    present month   total amount ----------------------------------------------------------------------------------------- divisionbhopa      april + may                june            april + may+ june  divisionsagar      april  + may               june            april + may+ june 

here not add oct amount because selected month june want data till june

for month of june, data should like:

name_of_office     sum upto previous month   present month    total  amount --------------------------------------------------------------------------- divisionbhopal           175                     100              275 divisionsagar           1120                     400             1520 

for october data should (if user selects december):

name_of_office     sum upto previous month   present month      total  amount ----------------------------------------------------------------------------- divisionbhopal          275                      125                400 divisionsagar          1520                      100               1620 

can try this

    select a.name_of_office, b.sum_upto_prev_month, a.amount,b.sum_upto_curr_month temp inner join  ( select name_of_office, sum(amount) sum_upto_prev_month  temp group name_of_office month < 6 ) b on a.name_of_office = b.name_of_office inner join ( select name_of_office, sum(amount) sum_upto_curr_month  temp group name_of_office month <= 6 ) c on a.name_of_office = c.name_of_office a.month = 6