P: n/a

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.  
Share this Question
P: n/a

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?  
P: n/a

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.  
P: n/a

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?  
P: n/a

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.  
P: n/a

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...  
P: n/a

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. :)  
P: n/a

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   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 2954
 replies: 7
 date asked: Jul 28 '06
