473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Fetch rows until quantity has been met

Hi,
I have a SQL Database and I wish to obtain records until the customer's quantity requested has been met. Here is what I mean.
Request Table
Expand|Select|Wrap|Line Numbers
  1. Product     Quantity   Price
  2. -----------------------
  3. Apple          10.0      5.00 
  4.  
here is the SQL query to obtain matches:
Expand|Select|Wrap|Line Numbers
  1.     select c.FName,
  2.             p.ProductName,
  3.             s.Description, 
  4.             s.Quantity, 
  5.             s.Price 
  6.           FROM requests r
  7.  
  8.         inner join sellers s on r.ProductID = s.ProductID
  9.         inner join products p on p.ProductID=s.ProductID 
  10.          inner join customers c on c.ID=s.C_ID       
  11.         where r.C_ID = 3 AND r.matchType='Price'
  12.         ORDER BY s.Price ASC 
  13.  
And here is the result:
Expand|Select|Wrap|Line Numbers
  1.          FName   |   ProductName    |     Description                 |  Quantity  |   Price
  2.         --------------------------------------------------------------------------=
  3.      compny1          Apple                    royal apples fm appleco.      5.0          5.00
  4.         daz            Apple                     sweet apples                6.0          5.50
  5.     company2         Apple                       Apples yum                   8.0          9.00  
  6.  
I want to display the full requested 10KG quantity by selecting the rows and updating the database i.e. the output should be:

apples @5kg from compny1 = 5.00
apples @5kg from daz = 5.50
total = 10.50

the Database should then show 'Daz' quantity being 1.0 KG left. However, I keep getting suck in doing this. I have tried to do the following:
Expand|Select|Wrap|Line Numbers
  1.   while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
  2.  
  3.         if($rows1['Quantity']==$quantityRequested){ //If the first row = 10KG output only this row.
  4. echo $rows1['FName'];
  5. echo $rows1['NameProduct'];
  6. echo $rows1['Quantity'];
  7. echo $rows1['Price'];
  8. }else{
  9. //stuck here check the next rows and see there is 6KG's .. We need 10KG Requested - 5KG from daz(row1) 
  10. //                                                                            -Remaining amount left(i.e.5KG)
  11. //hence, print the output specified above and UPDATE Database where quantity has been reduced by X amount.
  12. }
  13.  
Ok, So what the problem is. I need to 'Make up' the 10KG requested demand.

Now the 'Cheapest' way to do this is by sorting the available matches by Asc Price which I have.

Now we can see the First row shows 'compny1' selling 5KG for £5.00... So, we need 10KG hence we display row 1..
Next, as we have not met the 10KG demand yet (still need another 5KG) we look at the second row. We see 'daz' is selling 6KG. but we only need 5more KG's to make the 10KG demand.
Hence, what I wish to do is... UPDATE the table so that the first record is copied to another table and the second row is updated to 1.0kg remaining(6KG-5KG). And hence, display to the customer:
Your 10KG can be purchased by

compny1 5KG@5.00
daz 5KG@5.50
------
Total £10.50


So, that is my problem. I dont know how to keep checking the rows until the quantity requested is met. I guess I need some kind of 'counter' that keeps track of how much quantity has been added added 'So far' and then check how much more is needed from the next row.
Mar 3 '13 #1
4 1752
Rabbit
12,516 Expert Mod 8TB
Use a variable to keep a running tally of how much of the demand has been met. Loop, output, and update the rows until the demand has been met.
Mar 3 '13 #2
hi Rabbit,
How would I do this? As this is what I keep getting stuck on. Like what I need is...
while there are 'potential matches' keep looping... I do this using

Expand|Select|Wrap|Line Numbers
  1. while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
  2.  
which would list those matching sellers.

Then check.. If the first row == $requestQuantity then output only the first matching row .. i.e. quantity has been met..

Else check the second row and see how much quantity has been fullfilled (i.e. the first row quantity + how much more is needed) If the second row + first row = quantity requested then done. the demand has been met and hence the appropriate row is modified to reflect the reduced seller quantity else check the next row and repeat the process..

Hence, need to keep track of how much quantity has been ADDED so far and MINUS from the next row so meet the demand.

Does this make sense?

Thanks so much:)
Mar 3 '13 #3
Rabbit
12,516 Expert Mod 8TB
You should not check if the first row matches quantity. The first check is whether the running sum variable has exceeded the quantity requested, if not, then you add and update the row. In pseudo code, it would be something like this
Expand|Select|Wrap|Line Numbers
  1. running tally = 0
  2. loop through recordset
  3.    if running tally > quantity requested
  4.       exit loop
  5.    else
  6.       running tally = running tally + row amount
  7.       output row
  8.       update row
  9.    end if
  10. end loop
Mar 3 '13 #4
Rabbit thank you for your reply. I will try this method out also along with other ways that have been suggested. Thanks
Mar 3 '13 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound...
2
by: Michel Esber | last post by:
System scenario: Linux DB2 Workgroup Server V7 FixPack 13. I have a java application (JDBC v2 driver) that reads into memory a considerable amount of data (100k-300k rows), summarizes the...
2
by: gimme_this_gimme_that | last post by:
Ideally I'd like an equivalent of : current date - 5 days which works for minutes instead of days... --- But, assuming I have a table with a timestamp column named modified_date , how...
6
by: saeedeh | last post by:
hi to all i have some problem with fetching my form enteries. the problem is when i post some enteries through a form to a databse called 'applicant' and try to fetch and print it nothing appears.it...
5
by: Mahesh S | last post by:
Hi I would like to write a SQL select statement that would fetch rows numbered 50 to 100. Let me clarify, if i say "fetch first 10 rows only", it returns the first 10 rows from the resultset....
1
by: juancho8529 | last post by:
I ned know how set the number of rows obtained from a interbase 2.0 query, hi have tried with instructions has fetch rows, limit, top... but i didn't have found a correct result, Please if...
0
by: PRIYA6930 | last post by:
Hi, I have a situation where I am writing code to fetch rows from a db2 table bases on spcefic date. I have declared a cursor and passing the parameters to it this way :some date Eg
2
by: dev177 | last post by:
Hi, I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time...
3
by: nrain | last post by:
Hi This is nrain here in a pl/sql block, some rows are getting inserted into the table violating the primary key and going to exception. I need to fetch records and want to check what...
2
by: Henry J. | last post by:
I'm DB2 newbie. I need to remove large number of rows from a table. I don't want to fill up the transaction log space. I have the below SQL that I feed to the db2 command to repeatly remove a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.