Обсуждение: need help

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

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
>
>

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

> 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 


I think these are the sqls you are looking for:

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
and datetime BETWEEN '2010-1-01' AND '2012-12-31'


Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk

> > Now I really don't know how to do this.
> > 
> > can you advise me more ?
> > 
> > 
> > Thanks,
> > 
> > Dhaval
>
>
> I think these are the sqls you are looking for:
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
> and datetime BETWEEN '2010-1-01' AND '2012-12-31'


Sorry, that should have been:

For your 2 examples:

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
and datetime < '2012-11-30'

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
and datetime < '2012-12-31'

I'm not what the use of the 'from' date is in your examples.
Do you need to know the final destination of the product in that time period?
Or every destination location of the product in that time period?

Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Sorry, why do you need the joins?

Best,
Oliver

Enviado via iPhone

Em 21/02/2013, às 09:28 PM, Russell Keane <Russell.Keane@inps.co.uk> escreveu:

>>> Now I really don't know how to do this.
>>>
>>> can you advise me more ?
>>>
>>>
>>> Thanks,
>>>
>>> Dhaval
>>
>>
>> I think these are the sqls you are looking for:
>>
>> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
>> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
>> and datetime BETWEEN '2010-1-01' AND '2012-12-31'
>
>
> Sorry, that should have been:
>
> For your 2 examples:
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
> and datetime < '2012-11-30'
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location =
l.id
> and datetime < '2012-12-31'
>
> I'm not what the use of the 'from' date is in your examples.
> Do you need to know the final destination of the product in that time period?
> Or every destination location of the product in that time period?
>
> Regards,
>
> Russell Keane
> INPS
>
> Follow us on twitter | visit www.inps.co.uk
>
>
>
> --
> 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

On Thu, Feb 21, 2013 at 3:20 PM, denero team <deneroteam@gmail.com> wrote:
> 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.
>
[...]
>
> Now I really don't know how to do this.
>
> can you advise me more ?
>

I'm not really sure if you even know what you want, because the
examples you showed were just a:
select * from prodct_move where datetime < $given_date

but from the description you gave before i understood another thing,
so this is my only attempt to get an answer from thin air for you:

SELECT distinct on (p.name) p.name, l.name, datetime
FROM location l
INNER JOIN product_move m ON m.destination_location = l.id
INNER JOIN product p ON m.product_id = p.id
WHERE
m.datetime < '2012-12-31'
ORDER BY p.name, datetime DESC

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

> Sorry, why do you need the joins?
>
> Best,
> Oliver

Strictly speaking, for the examples and results given, the joins are pointless when you can get all the info from the
'move'table (but then the problem is like the 'hello world' of SQL)
 
But then the other 2 tables are completely redundant in the examples (even for context they're not very useful), so I
madethe assumption that he would also want to retrieve some other information about the products or locations and the
joinswould then be required.
 

I think the question may be missing some details about what is actually wanted...

Thanks Russell,

let me check the query.

On Fri, Feb 22, 2013 at 2:56 PM, Russell Keane <Russell.Keane@inps.co.uk> wrote:
>> Or every destination location of the product in that time period?
>
> Ok, I've had another look at this this morning on the assumption you need every location that a product has been in
thattime period.
 
> This also assumes you're getting all the data you're interested in from the product_move table (no need to join to
theother tables).
 
>
> The query will get:
> Every product_move item for each product between the 'from' and 'to' dates
> AND
> The most recent product_move item for each product before the 'from' date.
>
> SELECT id as move_id, product_id, destination_location as location_id
> FROM product_move
> where datetime between '2012-11-01' and '2012-12-31'
> union
> SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
> FROM product_move pm
> inner join
> (
>         SELECT product_id, max(datetime) as datetime
>         FROM product_move
>         where datetime < '2012-11-01'
>         group by product_id
> ) X
> on pm.product_id = X.product_id and pm.datetime = X.datetime
>
> Thus you will know where every product was coming into the period and every subsequent destination it was moved to
withinthat period.
 
> (although I'm still not sure this is what you want)
>
> Regards,
>
> Russell Keane
> INPS
>
> Follow us on twitter | visit www.inps.co.uk
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

> Or every destination location of the product in that time period?

Ok, I've had another look at this this morning on the assumption you need every location that a product has been in
thattime period.
 
This also assumes you're getting all the data you're interested in from the product_move table (no need to join to the
othertables).
 

The query will get:
Every product_move item for each product between the 'from' and 'to' dates
AND
The most recent product_move item for each product before the 'from' date.

SELECT id as move_id, product_id, destination_location as location_id
FROM product_move
where datetime between '2012-11-01' and '2012-12-31'
union
SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
FROM product_move pm
inner join
(SELECT product_id, max(datetime) as datetimeFROM product_movewhere datetime < '2012-11-01'group by product_id
) X
on pm.product_id = X.product_id and pm.datetime = X.datetime

Thus you will know where every product was coming into the period and every subsequent destination it was moved to
withinthat period.
 
(although I'm still not sure this is what you want)

Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Hey, Thanks Russell and all others.

The query worked well. I got result what I expected.


Thanks again,

Dhaval

On Fri, Feb 22, 2013 at 4:11 PM, denero team <deneroteam@gmail.com> wrote:
> Thanks Russell,
>
> let me check the query.
>
> On Fri, Feb 22, 2013 at 2:56 PM, Russell Keane <Russell.Keane@inps.co.uk> wrote:
>>> Or every destination location of the product in that time period?
>>
>> Ok, I've had another look at this this morning on the assumption you need every location that a product has been in
thattime period.
 
>> This also assumes you're getting all the data you're interested in from the product_move table (no need to join to
theother tables).
 
>>
>> The query will get:
>> Every product_move item for each product between the 'from' and 'to' dates
>> AND
>> The most recent product_move item for each product before the 'from' date.
>>
>> SELECT id as move_id, product_id, destination_location as location_id
>> FROM product_move
>> where datetime between '2012-11-01' and '2012-12-31'
>> union
>> SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
>> FROM product_move pm
>> inner join
>> (
>>         SELECT product_id, max(datetime) as datetime
>>         FROM product_move
>>         where datetime < '2012-11-01'
>>         group by product_id
>> ) X
>> on pm.product_id = X.product_id and pm.datetime = X.datetime
>>
>> Thus you will know where every product was coming into the period and every subsequent destination it was moved to
withinthat period.
 
>> (although I'm still not sure this is what you want)
>>
>> Regards,
>>
>> Russell Keane
>> INPS
>>
>> Follow us on twitter | visit www.inps.co.uk
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql