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.
7  3332 
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?
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.
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?
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.
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...
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. :)
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: John Hunter |
last post by:
I have a largish data set (1000 observations x 100 floating point
variables), and some of the of the data are missing. I want to try a
variety of clustering, neural network, etc, algorithms on the...
|
by: mike420 |
last post by:
In the context of LATEX, some Pythonista asked what the big
successes of Lisp were. I think there were at least three *big*
successes.
a. orbitz.com web site uses Lisp for algorithms, etc.
b....
|
by: David MacQuigg |
last post by:
Playing with Prothon today, I am fascinated by the idea of eliminating
classes in Python. I'm trying to figure out what fundamental benefit
there is to having classes. Is all this complexity...
|
by: Steven Bethard |
last post by:
I have a list of dictionaries. Each dictionary holds counts of various
'words', e.g.:
py> countdicts =
I need to select dicts with the constraint that the number of each
'word' totalled...
|
by: darrel |
last post by:
I have this structure:
mypage.aspx (class = mypage)
myusercontro.ascx
On the mypage.aspx I can declare a variable:
animal = "monkey"
I can read this from the UC by simply doing this:...
|
by: Jim |
last post by:
Hi guys,
I have an object which represents an "item" in a CMS "component" where
an "item" in the most basic form just a field, and a "component" is
effectively a table.
"item" objects can be...
|
by: kriz4321 |
last post by:
Hi
I have a array in which I need to count the number of ocurrence of a particular word
for eg I need to count no of times a word "test" , "test2" occurs in a array @list.
(The contents of the...
|
by: jonjonkershaw |
last post by:
I have two data tables in the dataset
datatable1 looks like such
increment
11/05/2008 - 10:30
11/05/2008 - 10:00
11/05/2008 - 9:30
etc
datatable2 looks like this
|
by: geraldjr30 |
last post by:
hi,
i am very new to php. started learning 3 days ago. i have figured out how to list MS Access table output in table format in PHP. but i need to know how i can have some output like...
|
by: nar0122 |
last post by:
//Nicholas Riseden
//CSCI 3300
//Assignment 4 Version 2
#include "tree.h"
#include "pqueue.h"
#include <string>
#include <fstream>
#include <iostream>
#include <stdio.h>
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
