> 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