Need help to construct this query...

[Alex T]

Folks

I'm looking for some help...

I am having a table with DATES and PRICES (say a history of stock
prices).

I'm trying to build a query that would return the maximum number of
consecutive (date wise) records with unchanged PRICES, ie

(...)
21/02/2000 91.36
22/02/2000 91.38
23/02/2000 91.44
24/02/2000 91.22
25/02/2000 91.36
26/02/2000 91.36
27/02/2000 91.36
28/02/2000 91.37
29/02/2000 94.17
01/03/2000 94.17 <- 1
02/03/2000 94.17 <- 2
03/03/2000 94.17 <- 3
04/03/2000 94.17 <- 4
05/03/2000 94.17 <- 5
06/03/2000 94.17 <- 6
07/03/2000 94.11
08/03/2000 94.12
(...)

Would return 6 (6 data points with the same price)

I feel that this should be possible with a query but it seems it's a
little bit too complex for my level of expertise...

Any help welcome

-AlexT

 

[Tom Ellison]

Dear Alex:

The procedure would be to find the row preceding the current row which
has a different price, then count the number of rows after that one
but before the current one.

In your example, find the row dated 29/02/2000. Count the number of
rows after this row and before the current row, say 06/03/2000. There
are 6 of them.

I'm going to assume the date column is unique. If it isn't, that
could cause some unexpected problems.

SELECT MyDate, MyPrice
(SELECT COUNT(*) FROM MyTable T1
WHERE T1.MyDate < T.MyDate
AND T1.MyDate >
(SELECT MAX(T2.MyDate) FROM MyTable
WHERE T2.MyDate < T.MyDate AND T2.MyPrice <> T.MyPrice))
AS UnchangedCount
FROM MyTable T
ORDER BY MyDate

Be sure to change the names of tables and columns above to those you
have in your database.

As there is a two level nested subquery in this, there is some doubt
that Jet would run it. If your Access database is MSDE you should
have not problem.

If what you want is to count the number of days (whether there is a
row recorded or not) a simpler method could be used, simply finding
the previous day with a different price and calculating days:

SELECT MyDate, MyPrice
DateDiff('d', (SELECT MAX(T1.MyDate) FROM MyTable
WHERE T1.MyDate < T.MyDate AND T1.MyPrice <> T.MyPrice)) - 1
AS UnchangedDays
FROM MyTable T
ORDER BY MyDate

This is something which Jet can probably handle just fine.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Les]

Hi,
I think you can do this with a totals query. Create a
new query, and add prices and dates. Click on the totals
button. Leave prices with "group by" clause change "group
by" under dates to be "max".

 

[Les]

Sorry, misread your post. My answer won't give you what
you want.