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 <denerot...@gmail.com> wrote: > 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
