From: Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
To: denero team <deneroteam@gmail.com>
Cc: Carlos Chapi <carlos.chapi@2ndquadrant.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: need help
Date: Thu, 21 Feb 2013 20:35:00 +0000
Message-ID: <76AB0A60-BB3E-4674-881A-F0DF0ECE7F0B@gmail.com> (raw)
In-Reply-To: <CACotaffP=gssaBJ6D9zkUb1H5bP5bMJXGwE5iWXJmwQ_AMVCLQ@mail.gmail.com>
References: <CACotafd7p+MR6famJHOTLQrCMrYRPRSEP8YrNR6PGQ7jNbgBjA@mail.gmail.com>
<CAAk_w-qkwCtrKP5FH4Xro0=xdKj=LAnBdG4ZaV1s7fd5_eRSRw@mail.gmail.com>
<CACotaffP=gssaBJ6D9zkUb1H5bP5bMJXGwE5iWXJmwQ_AMVCLQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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@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.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
>>
>>
>
>
> --
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@gmail.com, deneroteam@gmail.com, carlos.chapi@2ndquadrant.com
Subject: Re: need help
In-Reply-To: <76AB0A60-BB3E-4674-881A-F0DF0ECE7F0B@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox