Fifo?

fuajuar · #1 (**permalink**) 07-02-09, 04:30

Hello

Lets said I have a table with id, date, amount

id | date | amount
1 | 12/1/2000 | 10
1 | 12/2/2000 | 3
2 | 12/3/2000 | 6
1 | 12/4/2000 | 5

If there is a sale in id=1 that is 15 then amount from id=1 will be decreased sorted by oldest date. So the result :

id | date | amount
1 | 12/1/2000 | 0
1 | 12/2/2000 | 0
2 | 12/3/2000 | 6
1 | 12/4/2000 | 3

Can I do it in sql? Pls give me some direction.

healdem · #2 (**permalink**) 07-02-09, 04:34

does this have to be in SQL, personally I'd do this in the front end, but that may indicate my lack of knowledge of SQL

I'm pretty certain you can achieve this using a sub select

Not certain but DATE may be a reserved word in MySQL

r937 · #3 (**permalink**) 07-02-09, 06:17

Quote:

Originally Posted by fuajuar

Can I do it in sql?

no, do this in your application language

fuajuar · #4 (**permalink**) 07-02-09, 23:31

Do you have some good references or links to solve my problem?

Or somebody want to share his code?

healdem · #5 (**permalink**) 07-03-09, 03:27

are we supposed to make a guess as to what language you are using?

w3schools.com is always a good start
PHP: Hypertext Preprocessor and theres always

Asp.Net
thers perl, cgi, and god knows how many other languages out there to extract data from MySQL... theres Vb.NET, C, C++, C#, Java, theres C++ builder.......

what you are asking is pretty trivial
you need toknow the current stock (do a SQL query to find that)
then sum the current orders/sales (do)
then pull off the last deliveries (order by delivery date descending untill you have retireved the current sotck.

if your stock movements were in the same table its even easier you issue a single SQL statement to get the current stock.

I have yet to come accross what you are lookig for in a live app.

however I sauspect that you coudl do what you want within a sub query, but it should be a pig performance wise. Although if Rudy R937 says do it in your application language then I guess thats what you should do......

gvee · #6 (**permalink**) 07-03-09, 08:17

Healdem, think of fresh produce when picturing this problem. You want to sell the stuff with the earliest sell by date first

rampurhaat · #7 (**permalink**) 07-03-09, 10:31

Yes, you might be able to do it in SQL, but the code might become complicated. Is there any specific reason that you want an SQL on this one?

I would recommend any server-side language such as PHP, like Healdem also said.
_________________
Infrared Sauna

mike_bike_kite · #8 (**permalink**) 07-03-09, 11:06

Here's the psuedo code to do it if it helps:

Code:

# run sql to get total_amount for that id
# if total_amount > sales_amount
#     while sales_amount > 0
#          pull oldest date for this id where amount > 0
#          pull amount for this date
#          if amount > sales_amount
#              update amount = amount - sales_amount where this id and date
#              sales_amount = 0
#          else
#              sales_amount = sales_amount - amount
#              update amount = 0 where this id and date
# else
#     error as not enough

Mike

fuajuar · #9 (**permalink**) 07-03-09, 14:12

Thanks for your comments.

I got this SP from my friend, there is a new field that is number.

Code:

BEGIN

    SET @ctt = 0;
    SET @amount = 0;
    SET @orders = orders;
    SET @number = 0;
    SET @idxx = idx;

   WHILE @orders >  0 DO
        SET @query=  CONCAT('SELECT `amount_id`,`counter` FROM mytable WHERE `id` = ? ORDER BY DATE ASC LIMIT ?,1 INTO @amount,@number');
        PREPARE stmt1 FROM @query;
        EXECUTE stmt1 USING @idxx, @ctt;
        DEALLOCATE PREPARE stmt1;

        SET @temp = @orders - @amount;

        IF (@temp >= 0) THEN
                     SET @itung = 0;
                     SET @orders = @orders - @amount;
        ELSE
                     SET @itung = @amount - @orders;
                     SET @orders = 0;
        END IF;

                   SET @query=  'UPDATE  mytable SET `amount_id` = ? WHERE `counter` = ?';
                   PREPARE stmt1 FROM @query;
                   EXECUTE stmt1 USING @itung, @number;
                   DEALLOCATE PREPARE stmt1;

                  SET @ctt = @ctt + 1;
   END WHILE;

END


parameters : orders INTEGER,idx INTEGER

What do you think?