Thanks Russell, let me check the query.
On Fri, Feb 22, 2013 at 2:56 PM, Russell Keane <russell.ke...@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 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
