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