Apportioning a value over certain number of rows without looping
-
CREATE TABLE CP1 (
Ident Int,
QTY Int
)
CREATE TABLE CP2 (
Ident Int,
QTY Int
)
Insert Into CP1 Values (1, 7)
Insert Into CP1 Values (1, 3)
Insert Into CP1 Values (1, 5)
Insert Into CP2 Values (1, 13)
Backgroud:
You have bought 15 pairs of socks. They were scanned them in three chunks. 7 then 3 then 5.
The resultant table has as per CP1.
You then want to remove 13 pairs of socks.
How would you remove the socks per row considering if you only scanned 7 you can’t remove 13.
Your resultant table should be
Ident QTY Refund
1 7 7
1 3 3
1 5 3
In each table you could have more than one ident. i.e. You returning socks and shoes so you cannot have a hard coded variable with 13.
I do not want to use a loop and loop through it. Ateast, I would rather not.
Any clever ideas???
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
What is the purpose of table CP2? Is it only an aggregate of the data in CP1?
Your table has no unique identifier. How do you link the two tables?
Once you clean up your table design, you will be able to delete what you want to.
-
The tables are a bit crude, I know.
They just for testing...
The tables are joined by Ident. CP1 is "things that were bought" and CP2 is "things returned" and in no way an aggrigate.
The sum of CP1.QTY is 15, the sum of QTY in CP2 is 13. I want to remove a QTY of 13 from the 3 rows in CP1.
Results would be (CP1):
QTY Return
7 7
3 3
5 3
Another mistake I made is to not iinclude the returnedQTY in CP1 which is the field that has been updated.
I know, bad DDL. Sorry
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply