Re: need help - Mailing list pgsql-sql

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.chapi@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 <deneroteam@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
>
>