Re: need help - Mailing list pgsql-sql

From denero team
Subject Re: need help
Date
Msg-id CACotafcqw-02xE3pmKjiARjeB8e_UUm8T7aYM1mF+1eJAe38iw@mail.gmail.com
Whole thread Raw
In response to Re: need help  (denero team <deneroteam@gmail.com>)
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Denis Papathanasiou
Date:
Subject: Is using the xml column type a good idea for queries that need to be fast?
Next
From: Ashwin Jayaprakash
Date:
Subject: Update HSTORE record and then delete if it is now empty - What is the correct sql?