Getting Counts Based on Non-specified date ranges

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.

Re: Getting Counts Based on Non-specified date ranges

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?

Re: Getting Counts Based on Non-specified date ranges

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.

Re: Getting Counts Based on Non-specified date ranges

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?

Re: Getting Counts Based on Non-specified date ranges

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.

Re: Getting Counts Based on Non-specified date ranges

No bother wrote:
> 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.

I still think I'm right (kind of):

If you create a table of 'dates where the number of widgets is LESS
THAN 10' then, for any two successive rows in that table, the number of
times that 3 distinct consecutive days occur (and by definition, these
are days on which the widgets >=10) is equal to:

FLOOR (no_of_consecutive_days/3)

where no_of_consecutive_days is equal to:

end date
minus start date
minus any holidays [you will need to construct a separate 'holidays'
table for your region - holidays(holiday text,holidate datetime) or
something like that]
minus any days in between where dayofweek = 1
minus any days in between where dayofweek = 2
minus 2 (because you also want to exclude the start date and the end
date)

so the answer will be sum(floor(no_of_consecutive_days/3))

now you just have to turn that into something mysql can understand -
hint: the table of 'dates where the number of widgets is LESS THAN 10'
will need to have row numbers so that mysql can understand that the
rows are successive. An easy way to do this is like this:

set @i = 0;

SELECT @i := @i + 1 AS row_number, etc,etc...

Re: Getting Counts Based on Non-specified date ranges

strawberry wrote:
> No bother wrote:
>> 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.
>
> I still think I'm right (kind of):
>
> If you create a table of 'dates where the number of widgets is LESS
> THAN 10' then, for any two successive rows in that table, the number of
> times that 3 distinct consecutive days occur (and by definition, these
> are days on which the widgets >=10) is equal to:
>
> FLOOR (no_of_consecutive_days/3)
>
> where no_of_consecutive_days is equal to:
>
> end date
> minus start date
> minus any holidays [you will need to construct a separate 'holidays'
> table for your region - holidays(holiday text,holidate datetime) or
> something like that]
> minus any days in between where dayofweek = 1
> minus any days in between where dayofweek = 2
> minus 2 (because you also want to exclude the start date and the end
> date)
>
> so the answer will be sum(floor(no_of_consecutive_days/3))
>
> now you just have to turn that into something mysql can understand -
> hint: the table of 'dates where the number of widgets is LESS THAN 10'
> will need to have row numbers so that mysql can understand that the
> rows are successive. An easy way to do this is like this:
>
> set @i = 0;
>
> SELECT @i := @i + 1 AS row_number, etc,etc...
>

Thanks. I'll have to think about this, particularly when I have had
more than 2 hours of sleep. :)

Re: Getting Counts Based on Non-specified date ranges

No bother wrote:
> strawberry wrote:
> > No bother wrote:
> >> 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.
> >
> > I still think I'm right (kind of):
> >
> > If you create a table of 'dates where the number of widgets is LESS
> > THAN 10' then, for any two successive rows in that table, the number of
> > times that 3 distinct consecutive days occur (and by definition, these
> > are days on which the widgets >=10) is equal to:
> >
> > FLOOR (no_of_consecutive_days/3)
> >
> > where no_of_consecutive_days is equal to:
> >
> > end date
> > minus start date
> > minus any holidays [you will need to construct a separate 'holidays'
> > table for your region - holidays(holiday text,holidate datetime) or
> > something like that]
> > minus any days in between where dayofweek = 1
> > minus any days in between where dayofweek = 2
> > minus 2 (because you also want to exclude the start date and the end
> > date)
> >
> > so the answer will be sum(floor(no_of_consecutive_days/3))
> >
> > now you just have to turn that into something mysql can understand -
> > hint: the table of 'dates where the number of widgets is LESS THAN 10'
> > will need to have row numbers so that mysql can understand that the
> > rows are successive. An easy way to do this is like this:
> >
> > set @i = 0;
> >
> > SELECT @i := @i + 1 AS row_number, etc,etc...
> >
>
> Thanks. I'll have to think about this, particularly when I have had
> more than 2 hours of sleep. :)

Of course this line:

minus any days in between where dayofweek = 2

should read:

minus any days in between where dayofweek = 7