query - for how many days consecutive change is happening

[Oleg]

hi All

have the following scenerio (very simplified)
NAMEPRICEDATE
Jean2301-Jan
Jean2302-Jan
Jean4303-Jan
Jean3204-Jan
Jean3205-Jan
Ginger4501-Jan
Ginger4502-Jan
Ginger3403-Jan
Ginger3404-Jan
Ginger3405-Jan
Ginger3206-Jan
Ginger4507-Jan
Ginger4508-Jan
Ginger4509-Jan
Ginger4510-Jan
Ginger4511-Jan
Ginger4512-Jan


what i need to get back is -
Name
LastPrice
For How Many Days it was Unchanged consecutively
so - it's

Jean 32 2
Ginger 45 6

I tried combination of Average & Standard Diviation - but it returns 8 for Ginger instead of 6, snce '45' is used before

p.s. how do i paste excel columns ? sapse between the columns got lost

 

[dpw204]

hi All
have the following scenerio (very simplified)
NAMEPRICEDATE
Jean2301-Jan
Jean2302-Jan
Jean4303-Jan
Jean3204-Jan
Jean3205-Jan
Ginger4501-Jan
Ginger4502-Jan
Ginger3403-Jan
Ginger3404-Jan
Ginger3405-Jan
Ginger3206-Jan
Ginger4507-Jan
Ginger4508-Jan
Ginger4509-Jan
Ginger4510-Jan
Ginger4511-Jan
Ginger4512-Jan
what i need to get back is -
Name
LastPrice
For How Many Days it was Unchanged consecutively
so - it's

Jean 32 2
Ginger 45 6

I tried combination of Average & Standard Diviation - but it returns 8 for Ginger instead of 6, snce '45' is used before

p.s. how do i paste excel columns ? space between the columns got lost

I don't know of a query directive which can scan through records looking for a break in a sequence.

Two ideas:
Both require a bit of programming:
1. -PRE-populate the data: Update the underlying table to contain a sequence counter
- Name, Price, Date, IncrementCounter
In the forms used with the tables build code that would track the addition/removal of a row, and note whether the sequence has changed.
- Upon adding a new matching value record, increment the IncrementCounter
- Upon adding a new row that doesn't match the previous record, start the IncrementCounter field with one.

2. OR - Loop the table(s) in a script looking for breaks in the sequence.
- if there are a relatively small number of values, use an array to store the Counts, otherwise use table.

Good Luck.

Mayhaps someone else will know a more elegant solution.