Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Subject
- Posted on
- No bother
July 28, 2006, 7:48 pm
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. 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:
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
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:
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...
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...
Site Timeline
- » not null column with null defaults
- — Next thread in » MySQL Database Forum
- » Need a top mysql expert in India
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum