Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbleyl
Starting Member
21 Posts |
 Posted - 2002-01-31 : 11:58:43
|
| Hi,I need a check on these update statements.Got a table of parts:Manufacturer|Category|Part|Uses|Market|RankNeed to calculate Market & Rank.Market is the Uses / (Sum of the Uses of it's Category).Rank is the Sum of the Market of the Parts with more Uses in the Category.This is what I have so far---For Market:UPDATE Parts SET Market = Parts.Uses/A.TotalUses FROM (SELECT Manufacturer, Category, SUM([Uses]) as TotalUses From Parts GROUP BY Manufacturer, Category) as A INNER JOIN Parts ON Parts.Manufacturer = A.Manufacturer AND Parts.Category = A.Category WHERE A.Uses >= 1 --Avoid divide by zero.--For Rank:UPDATE Parts SET Parts.Rank = (SELECT SUM(Market) FROM Parts as P2 WHERE P2.Manufacturer = P1.Manufacturer AND P2.Category = P1.Category AND P2.Market >= P1.Market)FROM Parts as P1Does this make sense? |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-05 : 14:28:22
|
Hiya,Seems like the query for Market is right.Could you please post some sample data to make the Rank query easier to understand? It would work differently depending on the data.Also, quote:
Rank is the Sum of the Market of the Parts with more Uses in the Category.
what exactly does this mean? (Sum(market) + sum(uses) of it's category?)Best Regards.Sarah Berger MCSD |
 |
|
|
dbleyl
Starting Member
21 Posts |
Posted - 2002-02-08 : 00:09:36
|
| Hi, Thanks for responding. Basically, a few records might look like this:Manufacturer|Category|Part|Uses|Market|Rank1,10,f511,300,?,?1,15,rgg4,5,?,?1,15,sp34,100,?,?1,15,p2,8,?,?2,11,xd18,18,?,?Uses is the number of different products the part fits.Market is really just how a part compares to the rest of it's category. In the sample data, the market for rgg4 would be 5/113. sp34 would be 100/113. The rank is a little more complicated; it's supposed to be all the markets added together that are greater than the current part's. The way it would be read is "part x is in the top y percent of category z"in the sample data, the rank of rgg4 would be 8/113 + 100/113, and p2 would just be 100/113.The real data is much uglier, and requires alot of conversions to get the divison right. It also has millions of rows.I know I was just being lazy: I really did test the queries, but I threw them together so fast I wasn't really sure if it jived. They're replacing some client-side cursors that loop and are just too slow and ugly and not scaling very well... |
|
|
|
|
|
|
|
|
Need UPDATE w/ Calculations Sanity Check.