Help with Wierd Query 1

[saadabc]

Hi,
I have a database table as follows:


Product Date Price
------- ---- -----

productA 07/05/05 100
productB 07/08/05 54
productB 07/10/05 51
productA 07/14/05 97
productB 07/16/05 49
productA 07/19/05 97
productB 07/22/05 52
productB 07/25/05 54
productA 07/30/05 101
productB 08/03/05 52
productA 08/08/05 103
productB 08/12/05 54
productA 08/18/05 103
productB 08/25/05 52



I want to find out (through a SQL query) all the dates where the price of a product 'starts' to increase after having fallen or remaining static.

for e.g. price of productA decreased from 07/05 to 07/14 (it remained same on 07/19) then it picked up on 07/30/05 - therefore my query would pick up the row:

productA 07/30/05 101

similarly, it would pick up:

productB 07/2205 52

and

productB 08/12/05 54


How do I accomplish this - this is a tough query for me. I can use either T-SQL or Jet-SQL to accomplish this.

My guess is that I may have to use temporary tables to accomplish this - but if you can think of a query that doesn't use temp tables - that's fine too. so far - i have used temporary tables but can't nail this one.


Thanks.

 

[r937]

select t3.Product
     , t3.Date
     , t3.Price
  from daTable as t1
inner
  join daTable as t2
    on t1.Product = t2.Product
   and t2.Date =
       ( select min(Date)
           from daTable
          where Product = t1.Product
            and Date > t1.Product )
   and t1.Price >= t2.Price           
inner
  join daTable as t3
    on t1.Product = t3.Product
   and t3.Date =
       ( select min(Date)
           from daTable
          where Product = t2.Product
            and Date > t2.Product )
   and t2.Price < t3.Price

untested ;-)

r937.com | rudy.ca

 

[BoxHead]

Posted a solution in one of the other forums.

John

Use what you have,
Learn what you can,
Create what you need.

 

[saadabc]

Thanks - this works.