none
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]signature

    Wednesday, November 13, 2013 7:34 PM
    Moderator
  • 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]signature

    Wednesday, November 13, 2013 8:27 PM
    Moderator
  • 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:
    Standard 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]signature

    Thursday, November 14, 2013 10:08 AM
    Moderator
  • 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]signature

    Thursday, November 14, 2013 10:24 AM
    Moderator
  • 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]signature

    Thursday, November 14, 2013 10:29 AM
    Moderator
  • 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

    Q01

    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
    Q02

    * 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]signature

    Friday, November 15, 2013 5:39 AM
    Moderator
  • 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]signature

    Thursday, August 14, 2014 9:26 AM
    Moderator