SELECT move_id, product_id,destination_location as location_id FROM product_move Where datetime BETWEEN $first AND $last
Have you tried something like this? Best, Oliver Enviado via iPhone Em 21/02/2013, às 08:20 PM, denero team <denerot...@gmail.com> escreveu: > Hi, > > Thanks for replying me. yes you are right at some level for my case. > but its not what I want. I am explaining you a case by example. > > Consider following are data in each table > > Location : > id , name, code > 1, stock, stock > 2, customer, customer > 3, asset, asset > > Product : > id, name, code, location > 1, product1, p1, 1 > 2, product2, p2, 3 > > > Product_Move : > id, product_id, source_location, destination_location, datetime > 1, 1, Null, 1, 2012-10-15 10:00:00 > 2, 2, Null, 1, 2012-10-15 10:05:00 > 3, 2, 1, 3, 2012-12-01 09:00:00 > > Please review all data , you can see, current location of product1 > (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset). > > now i want to find location of all products for given period > > for example : 2012-11-01 to 2012-11-30, then i need result should be like > below > move_id, product_id, location_id > 1, 1, 1 > 2, 2, 1 > > another example : 2012-11-01 to 2012-12-31 > move_id, product_id, location_id > 1, 1, 1 > 2, 2, 1 > 3, 2, 3 > > Now I really don't know how to do this. > > can you advise me more ? > > > Thanks, > > Dhaval > > > On Fri, Feb 22, 2013 at 1:26 AM, Carlos Chapi > <carlos.ch...@2ndquadrant.com> wrote: >> Hello, >> >> Maybe this query can help you >> >> SELECT p.name, l.name >> FROM location l >> INNER JOIN product_move m ON m.source_location = location.id >> INNER JOIN product p ON m.product_id = p.id >> WHERE p.id = $product_id >> AND m.datetime < $given_date >> ORDER BY datetime DESC LIMIT 1 >> >> It will return the name of the product and the location for a given id and >> date. >> >> >> 2013/2/21 denero team <denerot...@gmail.com> >>> >>> Hi All, >>> >>> I need some help for my problem. >>> Problem : >>> I have following tables >>> 1. Location : >>> id, name, code >>> 2. Product >>> id, name, code, location ( ref to location table) >>> 2. Product_Move >>> id, product_id ( ref to product table), source_location (ref to >>> location table) , destination_location ( ref to location table) , >>> datetime ( date when move is created) >>> >>> now i want to know for given period of dates, where is the product >>> actually. >>> >>> can anyone help me ?? >>> >>> Thanks, >>> >>> Dhaval >>> >>> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
