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  3343 
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_consecut ive_days/3)
where no_of_consecuti ve_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(holida y 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_da ys/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:
>>
>NumWidge ts 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
>otherwis e 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
consecutiv e 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_consecut ive_days/3)
where no_of_consecuti ve_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(holida y 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_da ys/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:
>
NumWidget s 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_consecut ive_days/3)
where no_of_consecuti ve_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(holida y 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_da ys/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 data,
and to keep life simple I want to reduce the dimensions of the matrix
so that I have no missing values, since not all the algorithms are...
|
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. Yahoo store was originally written in Lisp.
c. Emacs
The issues with these will probably come up, so I might as well
mention them myself (which...
|
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 unecessary?
Here is an example of a Python class with all three types of methods
(instance, static, and class methods).
# Example from Ch.23,...
|
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 over all selected dicts doesn't exceed a given
MAX_VALUE. Right now, I do this by:
py> def select(dicts, n):
|
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:
mypage.animal
| | |
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 created and then added to "component" objects to
build up the component definition.
My dilemma comes in deciding how to read/write data to the...
|
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 array is around 100 lines)
Code:
open(FH3, "sample.txt");
while(<FH3>)
|
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 following, based on a table with detail data. Days of the week Monday through sunday are across the top. i want the counts for each day listed for each...
|
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: 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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
