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