Re: need help - Mailing list pgsql-sql

From Russell Keane
Subject Re: need help
Date
Msg-id 8D0E5D045E36124A8F1DDDB463D548557D1618F45F@mxsvr1.is.inps.co.uk
Whole thread Raw
In response to Re: need help  (Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>)
Responses Re: need help
List 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



pgsql-sql by date:

Previous
From: Oliver d'Azevedo Cristina
Date:
Subject: Re: need help
Next
From: Russell Keane
Date:
Subject: Re: need help