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 |
|
kmmchan
Starting Member
5 Posts |
 Posted - 2006-02-21 : 05:56:14
|
| HiI have a simplified table called Product with the following fieldsFr_Product Qty To_ProductG5294565 10000 Z545LB12AG5294565 13286 Z545LB12BNow I want to display the result as followToprd Toprdoldqty Toprdnewqty Frprd Frprdoldqty FrprdnewqtyZ545LB12A 10000 10000 G5294565 23286 13286Z545LB12B 13286 13286 G5294565 13286 0Toprdoldqty and Toprdnewqty comes from Qty column in Product table.The tricky part is the calculation for Froldqty and Frprdnewqty1. 23286 comes from sum of 10000+132862. Frdprdnewqty in row 1, comes from (23286-10000=13286)3. Frprdoldqty in row 2, is the new value for G5294565 which is 132864. Frdprdnewqty in row 2, comes from 13286-13286=0This means that, G5294565 will be split into 2 products Z545LB12A and Z545LB12B, with qty of 1000 and 13286 respectively, and qty of G5294565 will be deducted correctly once it is splitanyone have any idea how to go abt it? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-02-21 : 07:39:20
|
provide info specified here in that format:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxthen we'll be able to help you.because as i read this twice i have no idea what you need... the values and column names just mees me up...  Go with the flow & have fun! Else fight the flow  |
 |
|
|
kmmchan
Starting Member
5 Posts |
Posted - 2006-02-21 : 08:43:27
|
| How to calculate Frprdoldqty and Frprdnewqty. These 2 columns are derived columnCreate table Product(Fr_Product char(8) Qty int, To_Product char(8))insert into Product Values('G5294565',10000,'Z545LB12A')insert into Product Values('G5294565',13286,'Z545LB12B')I want to display data as belowTo_Product Qty Fr_Product Frprdoldqty FrprdnewqtyZ545LB12A 10000 G5294565 23286 13286Z545LB12B 13286 G5294565 13286 0ExplanationProduct G5294565 will be split into 2 subproducts Z545LB12A and Z545LB12B with quantity 10000 and 13286 respectively.So the original G5294565 quantity is the sum of(10000+13286=23286). This can be seen in the Frprdoldqty. Once it is split to Z545LB12A, the Frprdnewqty is 23286-10000=13286So after it has been split to Z545LB12A, G5294565 quantity will be reduced to 13286 (row2, column4). Again G5294565 will be split to Z545LB12B with quantity of 13286. With this, Frprdnewqty will be 13286-13286=0I hope this explain the problem. thanks |
|
|
|
kmmchan
Starting Member
5 Posts |
Posted - 2006-02-21 : 20:45:06
|
| How to calculate Frprdoldqty and Frprdnewqty. These 2 columns are derived columnCreate table Product(Fr_Product char(8) Qty int, To_Product char(8))insert into Product Values('G5294565',10000,'Z545LB12A')insert into Product Values('G5294565',13286,'Z545LB12B')I want to display data as belowTo_Product Qty Fr_Product Frprdoldqty FrprdnewqtyZ545LB12A 10000 G5294565 23286 13286Z545LB12B 13286 G5294565 13286 0ExplanationProduct G5294565 will be split into 2 subproducts Z545LB12A and Z545LB12B with quantity 10000 and 13286 respectively.So the original G5294565 quantity is the sum of(10000+13286=23286). This can be seen in the Frprdoldqty. Once it is split to Z545LB12A, the Frprdnewqty is 23286-10000=13286So after it has been split to Z545LB12A, G5294565 quantity will be reduced to 13286 (row2, column4). Again G5294565 will be split to Z545LB12B with quantity of 13286. With this, Frprdnewqty will be 13286-13286=0I hope this explain the problem. thanks |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-21 : 21:41:42
|
| How do you determine which product (A or B) will have Frprdoldqty = 23286 and which will have Frprdnewqty = 0?----------------------------------'KH'It is inevitable |
|
|
|
kmmchan
Starting Member
5 Posts |
Posted - 2006-02-22 : 01:44:23
|
| The quantity column in product table refers to the quantity for To_productThe quantity of product A is 10000The quantity of product B is 13286So, based ont he 2 sample rows, the product A and B comes from product G originally, So product G quantity is 10000+13286, which I want to calculate on the fly.Hope this explains |
|
|
|
|
|
|
|
|
complex query