Hi,

I have 2 tables. I need to calculate the values of the rows until the month/year where a deviation is found.

In addition I need to calculate all the values where no deviation exists.

create table #Payments ( [ReceiptNo] int
,[LineNu] int
,[IdNo]int
,[Compensation]decimal (10,2)
,[Providence]decimal (10,2)
,[ForMonth]int
,[ForYear] int)
create table #maxPremia (foryear int, maxPremia decimal (10,2))
insert into #maxPremia values (2011,1910.00)
insert into #maxPremia values (2012,3456.00)
insert into #payments values (1,1,222345,23,45,1,2011)
insert into #payments values (1,2,222345,45,45,2,2011)
insert into #payments values (2,2,222345,678.90,1234,3,2011)
insert into #payments values (2,1,222345,124.0,8900,4,2011)
insert into #payments values (2,3,222345,45,45,5,2011)
insert into #payments values (3,1,222345,6789.09,1208.00,6,2011)
insert into #payments values (3,1,222345,1234.09,1208.00,7,2011)

I need to return 2 different results

This one sums the total values of months 1+2 for year 2011 before the month of 3 (first occurrence of month that has a deviation) when the amount exceeds the value in table MaxPremia. I need to display in one row the total for months 1+2 and the values for month 3.

IdNo TotalPayments foryear formonth DeviationValue

222345 158 2001 3 1912.90

222345

158

2001

4

9024.00

The second output sums all the months (TotalPayments) that are within the limits ie months 1,2,5 and shows the deviation rows:

IdNo

TotalPayments

foryear

formonth

DeviationValue

222345

248

2001

3

1912.90

222345

248

2001

4

9024.00

222345

248

2001

6

7997.09

How can I achieve these results?

Thanks