Hi,
I have given a task but i have no idea on it.. it sound simple but in store procedure i can't have a clue at all.
Table Water
NameOfWater VARCHAR,
WaterAmount INT
Table Cup
Id int,
CupName VARCHAR,
Cup1Space INT,
Cup2Space INT
Table Report
CupName VARCHAR,
NameOfWater VARCHAR,
Cup_Cup1Space INT,
Cup_Cup2Space INT
Value in Water table
Pepsi, 60
Sprite, 300
Pepsi, 60
Value in Cup table
1, SmallCup, 50, 20
2, MediumCup, 110, 20
3, LargeCup, 500, 500
Logic
We place the total amount of same water into Cup1Space until max then follow by Cup2Space, and balance will move to next Cup(order by cup id) of Cup1Space then follow by Cup2Space until the amount is completely place
So, the final Report table will look like this
SmallCup, Pepsi, 50, 20 (from 120 distribute 70 left 50)
MediumCup, Pepsi, 50, 0 (medium cup having enuf space for 50)
LargeCup, Pepsi, 0, 0 (Useless since is nothing inside the cup, can discard)
SmallCup, Sprite, 50, 20 (from 300 distribute 70 left 230)
MediumCup, Sprite, 110, 20 (from 230 distribute 130 left 100)
LargeCup, Sprite, 100, 0 (large cup will have enuf space)
Anyone plz help.
Regards,
Hooked888