Asked by:
Calculate values

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
Question
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]
 Edited by pituachMVP, Moderator 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]
 Edited by pituachMVP, Moderator Wednesday, November 13, 2013 8:28 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.

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 math2. you ask for "the first month that..."
First can be only one and you got 2 records in your results.
SO please explainCheck 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]
 Edited by pituachMVP, Moderator Thursday, November 14, 2013 10:19 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]
 Edited by pituachMVP, Moderator 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]
 Edited by pituachMVP, Moderator Thursday, November 14, 2013 10:35 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

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]
 Edited by pituachMVP, Moderator Friday, November 15, 2013 5:52 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
 Proposed as answer by Kalman TothModerator Monday, November 18, 2013 10:17 PM
 Unproposed as answer by pituachMVP, Moderator Thursday, August 14, 2014 9:15 AM

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]
 Edited by pituachMVP, Moderator Thursday, August 14, 2014 9:28 AM