Re: need help

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 <deneroteam(at)gmail(dot)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(dot)chapi(at)2ndquadrant(dot)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(at)gmail(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql