Help with wierd query

[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.


Thanks.

 

[BoxHead]

I copied the values you posted and had to assume there was no primary key. I also had to assume that the dates would be unique to a product - ProductA would only have one price for a given day.

If that's true, the following should work.

SELECT tblPriceCheck.myProd, tblPriceCheck.myDate, tblPriceCheck.myPrice AS Price, 

IIf([myDate]=DMin("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """"),0,[myprice]-DLookUp("[myPrice]","tblPriceCheck","[myDate] = #" & DMax("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """" & " AND [myDate] < #" & [mydate] & "#") & "#")) AS PriceChange, 

IIf(IIf([myDate]=DMin("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """"),0,[myprice]-DLookUp("[myPrice]","tblPriceCheck","[myDate] = #" & DMax("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """" & " AND [myDate] < #" & [mydate] & "#") & "#"))>0,IIf(DLookUp("[myPrice]","tblPriceCheck","[myDate] = #" & DMax("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """" & " AND [myDate] < #" & [mydate] & "#") & "#")-DLookUp("[myPrice]","tblPriceCheck","[myDate] = #" & DMax("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """" & " AND [myDate] < #" & DMax("[myDate]","tblPriceCheck","[myProd] = " & """" & [myProd] & """" & " AND [myDate] < #" & [mydate] & "#") & "#") & "#")<=0,"START"),"") AS MovingUp

FROM tblPriceCheck
ORDER BY tblPriceCheck.myProd, tblPriceCheck.myDate;

I named my table tblPriceCheck with 3 fields: myProduct, myDate, and myPrice. Substitute your own names as appropriate.

The first three columns are from the table, sorted by myProd and then myDate.

The fourth column calculates the change from the previous date for that product.

The fifth column looks at the change in price for the record's date as well as the previous date for that product. If the change for the record is greater than zero and the change for the previous record is zero or less, the query returns "START", otherwise, it's "".

While looks like quite a mess, it's just using the Dmax function on the date field to identify the maximum date for a product that is less than the record's date. Then, it uses the same premise to look at the date before that to compare the actual changes in price.

Note that I did this in query design so there are no line breaks except the ones I've added to try to make it a bit easier to read.

HTH

John

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

 

[vongrunt]

OK, 1st derivation greater than zero, previous discrete point must have 1st derivation less or equal zero.

Pain in the a** to do with SQL because results are based on dependencies between table rows.

Speaking about weird queries, what about quad self-join and triple derived table?

select *
from
(	select Y.Product, Y.Date, Y.Price, Y.PrevDate, Y.prevPrice, Y.prev2Date, E.Price as prev2Price
	from
	(	select X.Product, X.Date, X.Price, X.PrevDate, C.Price as prevPrice, max(D.Date) as prev2Date
		from
		(	select A.Product, A.Date, A.Price, max(B.Date) as PrevDate 
			from blah A
			inner join blah B on B.Product=A.Product and B.Date < A.Date
			group by A.Product, A.Date, A.Price
		) X
		inner join blah C on C.Product=X.Product and C.Date=X.PrevDate
		inner join blah D on D.Product=X.Product and D.Date < X.PrevDate
		group by X.Product, X.Date, X.Price, X.PrevDate, C.Price
	) Y
	inner join blah E on E.Product=Y.Product and E.Date = Y.prev2Date
) W
where Price > prevPrice and prevPrice <= prev2Price
order by Product, Date

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)