Results 1 to 9 of 9

Thread: Fifo?

  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: Fifo?

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fuajuar
    Can I do it in sql?
    no, do this in your application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2008
    Posts
    3
    Do you have some good references or links to solve my problem?

    Or somebody want to share his code?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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......
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Healdem, think of fresh produce when picturing this problem. You want to sell the stuff with the earliest sell by date first
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2009
    Location
    New Delhi
    Posts
    2
    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

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  9. #9
    Join Date
    Dec 2008
    Posts
    3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •