Styjun

What is the source of the fear in the Hallow spell's extra Fear effect?

Mute single speaker?

Shoes for commuting

Looking for a big fantasy novel about scholarly monks that sort of worship math?

Was the lunar landing site always in the same plane as the CM's orbit?

Why does 8 bit truecolor use only 2 bits for blue?

Would you recommend a keyboard for beginners with or without lights in keys for learning?

In High Performance Liquid Chromatography, why are ratios of solvents used?

Are language and thought the same?

What's the point of a PROTOTYPE macro that merely expands to its arguments?

Why there is no wireless switch?

Ceiling fan electrical box missing female screw holes

Golfball Dimples on spaceships (and planes)?

How to create large inductors (1H) for audio use?

Do 643,000 Americans go bankrupt every year due to medical bills?

Does the Commodore CDTV-CR contain a 65C02 for some reason?

Matlab fmincon for a problem with many nonlinear constraints

What is the DC of the Tempest Cleric's Wrath of the Storm feature?

Remaining in the US beyond VWP admission period

How do German speakers decide what should be on the left side of the verb?

How does the UK House of Commons think they can prolong the deadline of Brexit?

Round away from zero

Infinitely many Primes

Is there any difference between these two sentences? (Adverbs)

updating transactions with preaggregated data

Solutions for INSERT OR UPDATE on SQL ServerSQL update from one Table to another based on a ID matchSQL update query using joinsHow can I do an UPDATE statement with JOIN in SQL?Update a table using JOIN in SQL Server?How do I UPDATE from a SELECT in SQL Server?What are the options for storing hierarchical data in a relational database?Insert into a MySQL table or update if existsSelecting rows based value in sql server 2008Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, or when the subquery is used as an expression

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a transactions table. I need to update the table with a pre-aggregated value from another table and then roll these down to varying levels of granularity.

However, the final output is incorrect. Hoping someone here can help me figure out how to go about this.

Table A:

 TransID BankName Location Region SaleType MonthlyPayment Weight

 1 BOA Boston East F 3000 3
 2 Mellon Pittsburgh East C 1000 3
 3 BOA Boston East C 2000 2
 4 BOA Boston East 1000 2


 Table B

 BanKname Location Region Sales
 BOA Boston East 500
 Mellon Pittsburgh East 1000


 Desired Output structure

 BankName Location Region SaleType AvgSales AvgMonthlyPayment

Issue is that when updating and doing the weighted average, each of the Boston transactions is getting the 500 sales. When added, total sales should be 1500 but is now 2500.

If I update Table A with Sales value from Table B, sales gets repeated for each saletype - so it throws off the final average sales.

 update is this: (Added new column sales in A)

 update a
 set a.sales = b.sales
 from tableA a join tableB b on a.bankname=b.bankname and 
 a.location=b.location and a.region = b.region


 weighted average from A is calculated like this:

 select bankname,location,region,saletype, 
 sum(case when sales is not null then sales*weight else 0 
 end)/sum(weight) as avgsales
 , sum(case when monthlypayment is not null then monthlypayment*weight 
 else 0 end)/sum(weight) as avgmonthlyp
 from tableA
 group by bankname,location,region,saletype

For each saletype, the sales is updated with value thus increasing the final value by that fold.

How can I update sales so that BOA only gets 500 and Mellon only gets 1000 and the total # sales is at 1500?

sql sql-server

share|improve this question

edited Mar 28 at 5:00

Lucky

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I honestly don't understand what is your problem.
  
  – Ajan Balakumaran
  Mar 28 at 4:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Problem is: if I update table A with the sales from Table B, each row in Table A gets the sales due to different saletypes - thus throwing off the final sales value when its weighted average is calculated. So question is how do I update the txns table with this aggregated value without losing the integrity of the data
  
  – Lucky
  Mar 28 at 5:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You need to get the sales for the different sales types and it seems like you can't move further without that since your weighted average requires to multiply sales by weight and weight differs with sales type.
  
  – Ajan Balakumaran
  Mar 28 at 5:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And thats where the problem is, I dont have sales by different sale types - i only have aggregated sales. Hence the question here.
  
  – Lucky
  Mar 28 at 5:53
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Your join is causing duplication so it is probably not a valid join
  
  – wavery
  Mar 28 at 6:14
  

  
  
  
  

 | 
show 3 more comments

0

I have a transactions table. I need to update the table with a pre-aggregated value from another table and then roll these down to varying levels of granularity.

However, the final output is incorrect. Hoping someone here can help me figure out how to go about this.

Table A:

 TransID BankName Location Region SaleType MonthlyPayment Weight

 1 BOA Boston East F 3000 3
 2 Mellon Pittsburgh East C 1000 3
 3 BOA Boston East C 2000 2
 4 BOA Boston East 1000 2


 Table B

 BanKname Location Region Sales
 BOA Boston East 500
 Mellon Pittsburgh East 1000


 Desired Output structure

 BankName Location Region SaleType AvgSales AvgMonthlyPayment

Issue is that when updating and doing the weighted average, each of the Boston transactions is getting the 500 sales. When added, total sales should be 1500 but is now 2500.

If I update Table A with Sales value from Table B, sales gets repeated for each saletype - so it throws off the final average sales.

 update is this: (Added new column sales in A)

 update a
 set a.sales = b.sales
 from tableA a join tableB b on a.bankname=b.bankname and 
 a.location=b.location and a.region = b.region


 weighted average from A is calculated like this:

 select bankname,location,region,saletype, 
 sum(case when sales is not null then sales*weight else 0 
 end)/sum(weight) as avgsales
 , sum(case when monthlypayment is not null then monthlypayment*weight 
 else 0 end)/sum(weight) as avgmonthlyp
 from tableA
 group by bankname,location,region,saletype

For each saletype, the sales is updated with value thus increasing the final value by that fold.

How can I update sales so that BOA only gets 500 and Mellon only gets 1000 and the total # sales is at 1500?

sql sql-server

share|improve this question

edited Mar 28 at 5:00

Lucky

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I honestly don't understand what is your problem.
  
  – Ajan Balakumaran
  Mar 28 at 4:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Problem is: if I update table A with the sales from Table B, each row in Table A gets the sales due to different saletypes - thus throwing off the final sales value when its weighted average is calculated. So question is how do I update the txns table with this aggregated value without losing the integrity of the data
  
  – Lucky
  Mar 28 at 5:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You need to get the sales for the different sales types and it seems like you can't move further without that since your weighted average requires to multiply sales by weight and weight differs with sales type.
  
  – Ajan Balakumaran
  Mar 28 at 5:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And thats where the problem is, I dont have sales by different sale types - i only have aggregated sales. Hence the question here.
  
  – Lucky
  Mar 28 at 5:53
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Your join is causing duplication so it is probably not a valid join
  
  – wavery
  Mar 28 at 6:14
  

  
  
  
  

 | 
show 3 more comments

I have a transactions table. I need to update the table with a pre-aggregated value from another table and then roll these down to varying levels of granularity.

However, the final output is incorrect. Hoping someone here can help me figure out how to go about this.

Table A:

 TransID BankName Location Region SaleType MonthlyPayment Weight

 1 BOA Boston East F 3000 3
 2 Mellon Pittsburgh East C 1000 3
 3 BOA Boston East C 2000 2
 4 BOA Boston East 1000 2


 Table B

 BanKname Location Region Sales
 BOA Boston East 500
 Mellon Pittsburgh East 1000


 Desired Output structure

 BankName Location Region SaleType AvgSales AvgMonthlyPayment

Issue is that when updating and doing the weighted average, each of the Boston transactions is getting the 500 sales. When added, total sales should be 1500 but is now 2500.

If I update Table A with Sales value from Table B, sales gets repeated for each saletype - so it throws off the final average sales.

 update is this: (Added new column sales in A)

 update a
 set a.sales = b.sales
 from tableA a join tableB b on a.bankname=b.bankname and 
 a.location=b.location and a.region = b.region


 weighted average from A is calculated like this:

 select bankname,location,region,saletype, 
 sum(case when sales is not null then sales*weight else 0 
 end)/sum(weight) as avgsales
 , sum(case when monthlypayment is not null then monthlypayment*weight 
 else 0 end)/sum(weight) as avgmonthlyp
 from tableA
 group by bankname,location,region,saletype

For each saletype, the sales is updated with value thus increasing the final value by that fold.

How can I update sales so that BOA only gets 500 and Mellon only gets 1000 and the total # sales is at 1500?

sql sql-server

share|improve this question

edited Mar 28 at 5:00

Lucky

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

Table A:

 TransID BankName Location Region SaleType MonthlyPayment Weight

 1 BOA Boston East F 3000 3
 2 Mellon Pittsburgh East C 1000 3
 3 BOA Boston East C 2000 2
 4 BOA Boston East 1000 2


 Table B

 BanKname Location Region Sales
 BOA Boston East 500
 Mellon Pittsburgh East 1000


 Desired Output structure

 BankName Location Region SaleType AvgSales AvgMonthlyPayment

 update is this: (Added new column sales in A)

 update a
 set a.sales = b.sales
 from tableA a join tableB b on a.bankname=b.bankname and 
 a.location=b.location and a.region = b.region


 weighted average from A is calculated like this:

 select bankname,location,region,saletype, 
 sum(case when sales is not null then sales*weight else 0 
 end)/sum(weight) as avgsales
 , sum(case when monthlypayment is not null then monthlypayment*weight 
 else 0 end)/sum(weight) as avgmonthlyp
 from tableA
 group by bankname,location,region,saletype

sql sql-server

sql sql-server

share|improve this question

edited Mar 28 at 5:00

Lucky

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

share|improve this question

edited Mar 28 at 5:00

Lucky

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges

asked Mar 28 at 4:48

LuckyLucky

6177 bronze badges