Updating records based upon aggregate values in a different field

• charles.banks 37259

  Newbie

  Points: 7

  More actions

  September 10, 2015 at 1:57 pm

  #150408

  I apologize for such a simple question but I am struggling with a solution to the following. I can't even figure out how to appropriately ask or format the question. 😀

  I should note this is on a MS SQL Server 2014 box if that makes any difference.

  In an attempt to make it easier to work with the data

  [Code="sql"]

  CREATE TABLE #MyTable

  (

  PartyIDInt,

  Party_PerIDInt,

  CheckNoInt,

  Check_PerID Int,

  ItemIDInt,

  SalesMoney,

  Itm_PerIDInt

  )

  Insert Into #MyTable

  (PartyID, Party_PerID, CheckNo, Check_PerID, ItemID, Sales, Itm_PerID)

  Select 1,0,10,0,2,100,1 Union

  Select 1,0,10,0,4,150,2 Union

  Select 1,0,20,0,6,160,1 Union

  Select 1,0,20,0,8,80,2 Union

  [/code]

  Original table looks like the following:

  PartyID,Pty_PerID,CheckNo,Check_PerID,ItemID,Sales,Itm_PerID

  1,0,10,0,2,100,1

  1,0,10,0,4,150,2

  1,0,20,0,6,160,1

  1,0,20,0,8,80,2

  What I am attempting to accomplish is as follows:

  Update Pty_PerID to the ID that is in Itm_PerID based upon the maximum sales volume for the Party

  •Pty_PerID would be updated to “1” because 100+160 > 150+80

  Update Check_PerID to the ID that is in Itm_PerID based upon the maximum sales volume for the Check.

  •Itm_PerID would be updated to “2” on CheckNo 10 because 150>100

  •Itm_PerID would be updated to “1” on CheckNo 20 because 160>80

  Updated table should look like the following:

  PartyID,Pty_PerID,CheckNo,Check_PerID,ItemID,Sales,Itm_PerID

  1,1,10,2,2,100,1

  1,1,10,2,4,150,2

  1,1,20,1,6,160,1

  1,1,20,1,8,80,2