> 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 that time 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 other 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 within that 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
