|
Posted by No bother on August 1, 2006, 1:17 pm
Please log in for more thread options strawberry wrote:
> No bother wrote:
>> strawberry wrote:
>>> No bother wrote:
>>>> I have a table which has, among other fields, a date field. I want to
>>>> get a count of records where certain criteria are met for, say, three
>>>> days in a row. For example:
>>>>
>>>> NumWidgets Date
>>>> 1 1/1/2000
>>>> 10 1/2/2000
>>>> 20 1/3/2000
>>>> 10 1/4/2000
>>>> 15 1/5/2000
>>>> 5 1/6/2000
>>>>
>>>> I would like to know how many times 3 consecutive days have at least 10
>>>> widgets. In this case the answer is 1 because from 1/1/2000 to 1/3/2000
>>>> one day did not have at least 10 widgets, from 1/2/2000 to 1/4/2000 each
>>>> day had at least 10 widgets, from 1/3/2000 to 1/5/2000, though all three
>>>> days have at least 10 widgets, 1/3/2000 was already counted before so it
>>>> should not count again, and from 1/4/2000 to 1/6/2000 one day did not
>>>> have a least 10 widgets. Since 1/3/2000 was not counted before it would
>>>> otherwise qualify in the next set.
>>>>
>>>> I am hoping to do this in a query and not have to iterate manually
>>>> through the entire table.
>>> So, actually you want to select all days where the number of widgets is
>>> less than 10, and then, from that set, count the number of times
>>> that`the number of days between two consecutive dates is greater than 3?
>>>
>> No. I need to know how many times within the table three consecutive
>> days each have at least 10 widgets. The problem with first selecting
>> the days is that I have to use a custom function to filter out holidays
>> and weekends. I should be able to tell if a given date range is
>> consecutive for my purposes but I would think that would have to be done
>> before filtering out other data. Also, I need to make sure I am not
>> counting any given date twice.
>
> So, actually you want to select all days where the number of widgets is
> less than 10, and then, from that set, count the number of times
> that`the number of days (minus weekends and holidays) between two
> consecutive dates is greater than 3?
>
No. To phrase things as closely to your syntax as I can, I actually
want to select all days where the number of widgets is at least 10
(meaning, 10 or more), and then, from that set, count the number of
times that there are 3 consecutive dates, with the provision that a
date counted in one set is not counted in another set. So, if 4
consecutive days have at least 10 widgets each then the count I need is
1, not 2. If six consecutive days have at least 10 widgets each then the
count is 2. If two consecutive days have at least 10 widgets, followed
by a day that did not have at least 10 widgets, which is followed by a
day with at least 10 widgets, then the count is zero.
| 
XML Sitemap