# Calculate values

### Question

• 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

Wednesday, November 13, 2013 6:38 PM

### All replies

• Can you try to clarify again the result you're looking for?
I can not see how you got the value of 158 in the result

* "total values of months 1+2 for year 2011 before the month of 3" = 23 + 45

total of what? what value do you sum?

[Personal Site] [Blog] [Facebook]

Wednesday, November 13, 2013 7:34 PM
• Hi

158 are the sum for months 1 and 2 before month 3 where the first deviation is found 1912.90.

Wednesday, November 13, 2013 7:37 PM
• again.. sorry:

"sum for months 1 and 2" what value do you sum from the month? what column do you sum?

i was trying to play with "standard deviation" and with "standard deviation using Bessel's Correction" and i can't get your values :-(

can you show us the math? show us how do you get the value please

[Personal Site] [Blog] [Facebook]

Wednesday, November 13, 2013 8:27 PM
• By deviation which is probably the wrong term i mean that the total exceed the amount in maxPremia table for that year.

I sum compensation+providence

(

23.00 45.00
45.00 45.00)

for months 1 and 2 as after that month 3 has a total  per month/year  that exceeds the amount in maxPremia table.

I need the total for the months prior to the first month that has the amount that exceeds the amount in maxPremia table.

In addition i also need total for months 1,2,5 as these are all the months that their sum per month/year don't exceed the amount in maxPremia table.

Thursday, November 14, 2013 5:43 AM
• OK...

First of all this has nothing to do with deviation :-)
deviation is a measure of difference between the observed value of a variable and some other value, often that variable's mean.

This is the formula for deviation:

and in SQL Server we use the deviations function like STDEV & STDEVP

Back to your question...

I got the idea how you get value of TotalPayments (158, and 248). but still i don't get:

1. how you got the value of DeviationValue in the result set. is this just compensation+providence ?
again pleas show us the math

2. you ask for "the first month that..."
First can be only one and you got 2 records in your results.
SO please explain

Check this query:

```-- WHERE M.maxPremia < compensation+providence
SELECT TOP 1 P.*, M.maxPremia, (select SUM(compensation+providence) from Payments PIn where PIn.ForMonth < P.ForMonth), compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia```
Is this near what you are looking for?

[Personal Site] [Blog] [Facebook]

Thursday, November 14, 2013 10:08 AM
• And for the second request is this near what you are looking for?

```SELECT
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where PIn.ForMonth in (1,2,5)),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where P.ForMonth not in (1,2,5)```

* pay attention i did not use temporary tables so in my query there is no # in the table name

** How do chose dynamically the value (1,2,5), or is this a constant?

[Personal Site] [Blog] [Facebook]

Thursday, November 14, 2013 10:24 AM
• OK i got it i think :-)

Is this what you are looking for?

```SELECT TOP 1
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where PIn.ForMonth < P.ForMonth),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia

SELECT
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where compensation+providence < M.maxPremia),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia```

If so then we can do it in a better way... first i want to know if this is what you are looking for, and then we shell move to Optimization (For this we need to know what version of SQL Server you are using)

[Personal Site] [Blog] [Facebook]

Thursday, November 14, 2013 10:29 AM
• Hi Pituach

Thanks for the help.

I get the following results when running your last query

```SELECT
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where compensation+providence < M.maxPremia),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia```

 ReceiptNo LineNu IdNo Compensation Providence ForMonth ForYear TotalPayments foryear maxPremia 1 1 222345 23.00 45.00 1 2011 68.00 2011 1910.00 1 2 222345 45.00 45.00 2 2011 158.00 2011 1910.00

What i need is the following:

 ReceiptNo LineNu IdNo foryear formonth [Compensation]+[Providence]) Total  rows where [Compensation]+[Providence])< 1910.00 2 1 222345 2011 4 9024.00 2160.9 3 1 222345 2011 6 7997.09 2160.9

I don't calculate month 7 as the max month where [Compensation]+[Providence]>1910 is 6

Thursday, November 14, 2013 8:00 PM
• Hi

I am sorry but i just dont get you and time for guessing is over :-)

This the result I get using your DDL+DML

```SELECT TOP 1
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where PIn.ForMonth < P.ForMonth),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia```

```SELECT
P.*, M.maxPremia,
(select SUM(compensation+providence) from Payments PIn where compensation+providence < M.maxPremia),
compensation+providence
from Payments P
left JOIN maxPremia M on P.ForYear = M.foryear
where compensation+providence > M.maxPremia```

* I have no idea why you say that using the query i sent you get this result

 ReceiptNo LineNu IdNo Compensation Providence ForMonth ForYear TotalPayments foryear maxPremia 1 1 222345 23.00 45.00 1 2011 68.00 2011 1910.00 1 2 222345 45.00 45.00 2 2011 158.00 2011 1910.00

* Moreover you asked those result before:

 IdNo TotalPayments foryear formonth DeviationValue 222345 248 2001 3 1912.90 222345 248 2001 4 9024.00 222345 248 2001 6 7997.09

and now you want those result which are not the same!

What i need is the following:

 ReceiptNo LineNu IdNo foryear formonth [Compensation]+[Providence]) Total  rows where [Compensation]+[Providence])< 1910.00 2 1 222345 2011 4 9024.00 2160.9 3 1 222345 2011 6 7997.09 2160.9

I don't calculate month 7 as the max month where [Compensation]+[Providence]>1910 is 6

* There is new value 2160.9 and again you talk about "Total  rows" we cant sum rows! we can only sum values in specific column in the row, which you don't tell us! "Total" of what ?!?

* there is no max month to calculate. max month is 7 as we have months from 1 to 7.

the  max month where [Compensation]+[Providence]>1910  is 7 and not 6 !!!
at month 7 we got [Compensation]+[Providence] = 2442.09 and since 2442.09 > 1910 then this is the max month that fit the filter and not month 6.

I hope someone else understand you better, sorry at this time i can't spent any more time on guessing. if i will get a clean explanation from scratch, a question that is ask as should be and using the right calculation i might do another try... maybe it is time to write it from scratch using word document, read it before and make sure all is explained.

I hope you will get what you need :-)
Good Luck,

[Personal Site] [Blog] [Facebook]

Friday, November 15, 2013 5:39 AM
• Hi Pituach,

I don't know I got different results when i ran your query the first time but now i get the same results as you. However, I have another issue. If I have the following data:

```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,12.09,12.00,7,2011)```

Then only until month 6 the total compensation+providence per month,receiptno,linenu exceeds the maxPremia (months  3,4,6 exceed the maxpremia). I want to calculate the total sum for all the rows where compensation+providence <maxPremia and not including the month that is after the max month where

compensation+providence>maxpremia. This means that i will calculate the total for months 1,2,5 only and not month 7). However, the query calculates also month 7.

Thanks

Saturday, November 16, 2013 5:03 PM
• Hi Pituach,

I don't know I got different results when i ran your query the first timebut now i get the same results as you. However, I have another issue. If I have the following data:

Hi collie12

I hope you are still here:-)
If I understand you correctly my solution did answer your first question, and now you have a followup question. true?

In this case, and since this thread is long and quite old, Please close this thread by marking the answers that you got (+ you welcome to vote for useful responses as well), and open a new thread for the new question. You can add in the content a link to previous thread (this one) in order to give the reader the "full image" or the background :-)

>>  Kalman you have just Propose the new question as answer, instead of the answers :-(
I unproposed it as answer of course :-)

[Personal Site] [Blog] [Facebook]

Thursday, August 14, 2014 9:26 AM