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 |
|
abhijeetdighe
Starting Member
24 Posts |
 Posted - 2007-10-15 : 06:04:51
|
| Hi Experts I have appno,Qty,Price in my table and I want to update CleanQty for that application no as per rules.Here is the o/p I want in CleanQty Column with explanation about How CleanQty calculated :AppNo Qty Price CleanQty 1 200 40 100 (Step 2) 1 100 50 100 (Step 1)1 300 30 100 (Step 3) 2 300 50 300 (Step 4)2 200 40 0 (Step 5)2 100 30 0 (Step 6) 3 100 50 0 (Step 8) 3 200 50 200 (Step 7)3 300 40 100 (Step 9)Explanation about how CleanQty is calculated :(Step 1 : Max price for app1 is 50 , so put its corresponding qty here which is 100)(Step 2 : 2nd highest price for App1 is 40.So cleanQty 100=200(current Qty) - 100(prev. Clean qty) (Step 3 : 3rd highest price for App1 is 30. So cleanQty 100=300(current Qty)-200(sum of prev. clean qty is 100+100) (Step 4 : Max Price for app2 is 50 , so put its corresponding qty here which is 300)(Step 5 : 2nd highest price for app2 is 40.So cleanQty -100=200(current Qty) - 300(prev. Clean qty).(Step 6 : 3rd highest price for app2 is 30. So cleanQty -200=100(current Qty)-300(sum of prev. clean qty is 0+300) Clean Quantity is -ve so put 0) (Step 7 : Max Price for app3 is 50 and another record for app3 is having price 50 So which one is having max Qty choose that, so put its corresponding qty here which (Step 8 : 100-200=-100 So 0) (Step 9 : 300-(0+200) =100) Plz help me to write optimized query to calculate CleanQtyThanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 06:49:03
|
Do you need to calculate this at database?What about yout front-end? Can the application calculate the running total?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 07:18:17
|
Strange that when a problem is URGENT, the original poster always finds time to post the problem statement on several different sites rather than do some coding...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Complex Query