Hi Charles,
OK, didn't realise there were three columns.
Can you write some code like this:
CREATE CURSOR c_Raw;
(datefield d,;
timefield c(10),;
amount i)
INSERT INTO c_Raw VALUES (DATE(2001,8,1), "6:21 pm", 130)
INSERT INTO c_Raw VALUES (DATE(2001,9,1), "10:46 pm", 148)
INSERT INTO c_Raw VALUES (DATE(2001,9,1), "6:08 pm", 116)
for all of your data, then copy and paste it here so that I can create
your data in a cursor easily, please?
By providing code like and an example of what your output is to look
like, you will most times get a much faster/better response to your
questions.
Frank.
Frank Cazabon
On 01/05/2013 10:49 PM, Charles Hart Enzer, M.D., FAACP wrote:
Dear Frank:
You have a keen eye.
My bad. The column headers were off. There are three columns:
* Date
* Time
* Amount
Thus:
DATE TIME AMOUNT
01/08/2001 6:21 pm 130
01/09/2001 10:46 pm 148
01/09/2001 6:08 pm 116
01/10/2001 11:07 pm 148
01/10/2001 6:18 pm 138
01/10/2001 1:11 pm 122
01/10/2001 7:31 am 135
01/11/2001 9:53 pm 141
01/11/2001 5:56 pm 167
01/11/2001 7:36 am 141
01/12/2001 6:55 pm 131
01/12/2001 8:02 am 156
01/13/2001 6:21 pm 111
01/13/2001 12:16 pm 138
01/13/2001 8:34 am 175
01/13/2001 1:50 am 189
01/14/2001 6:07 pm 109
01/14/2001 11:58 am 95
01/14/2001 11:51 am 149
01/14/2001 8:06 am 151
01/14/2001 12:06 am 144
01/15/2001 11:28 pm 143
01/15/2001 6:18 pm 103
01/15/2001 12:04 pm 90
01/15/2001 7:44 am 116
01/15/2001 2:33 am 160
01/16/2001 11:28 pm 156
01/16/2001 11:04 pm 126
01/16/2001 6:01 pm 156
01/16/2001 7:57 am 156
01/17/2001 10:18 pm 111
01/17/2001 6:13 pm 126
01/17/2001 1:11 pm 158
01/17/2001 8:01 am 142
I want to get the number of records over a rolling XX Days --
actually, 21 Days.
This gets the number of records per day:
SELECT ;
Gluctmp1.date AS DailyDate, ;
COUNT(Gluctmp1.amount) AS DayCount;
FROM Gluctmp1;
WHERE ;
Gluctmp1.category = "Glucos";
GROUP BY Gluctmp1.date ;
ORDER BY Gluctmp1.date
-- Charles --
Website: http://homepages.uc.edu/~enzerch/
<http://homepages.uc.edu/%7Eenzerch/>
*"Medicine is a science of uncertainty and an art of probability"
*William Osler, M.D.
***Stop **Spammers**and **Virus Propagation***
***Before **forwarding**, please **delete**the history of all email
address***
On 5/1/2013 9:14 PM, Frank Cazabon wrote:
Three things I have noticed:
1. You are using a Datetime field, not a date, so you cannot add 21
to a date time and expect the resultant to be 21 days in the future.
You are actually adding 21 seconds. Either convert the datetime to a
date and then add 21 or add 21 * 24 * 60 * 60 (or whatever the
calculation is to work out how many seconds in a day.
2. Your SQL will not group by the date part of your date time field,
so you will not get what you expect. You need to group by just the
date portion of the date time field.
3. Are you sure you want to count the amount field? Maybe you should
be summing it instead?
Frank.
Frank Cazabon
On 01/05/2013 08:24 PM, Charles Hart Enzer, M.D., FAACP wrote:
Dear Frank:
As the saying goes, a picture is worth a thousand words.
*Raw Data:
*
DATE TIME AMOUNT
01/08/2001 6:21 pm 130
01/09/2001 10:46 pm 148
01/09/2001 6:08 pm 116
01/10/2001 11:07 pm 148
01/10/2001 6:18 pm 138
01/10/2001 1:11 pm 122
01/10/2001 7:31 am 135
01/11/2001 9:53 pm 141
01/11/2001 5:56 pm 167
01/11/2001 7:36 am 141
01/12/2001 6:55 pm 131
01/12/2001 8:02 am 156
01/13/2001 6:21 pm 111
01/13/2001 12:16 pm 138
01/13/2001 8:34 am 175
01/13/2001 1:50 am 189
01/14/2001 6:07 pm 109
01/14/2001 11:58 am 95
01/14/2001 11:51 am 149
01/14/2001 8:06 am 151
01/14/2001 12:06 am 144
01/15/2001 11:28 pm 143
01/15/2001 6:18 pm 103
01/15/2001 12:04 pm 90
01/15/2001 7:44 am 116
01/15/2001 2:33 am 160
01/16/2001 11:28 pm 156
01/16/2001 11:04 pm 126
01/16/2001 6:01 pm 156
01/16/2001 7:57 am 156
01/17/2001 10:18 pm 111
01/17/2001 6:13 pm 126
01/17/2001 1:11 pm 158
01/17/2001 8:01 am 142
SELECT ;
Gluctmp1.date AS DailyDate, ;
COUNT(Gluctmp1.amount) AS DayCount;
FROM Gluctmp1;
WHERE ;
Gluctmp1.category = "Glucos";
GROUP BY Gluctmp1.date ;
ORDER BY Gluctmp1.date
*Yields:*
DAILYDATE DAYCOUNT
01/08/2001 1
01/09/2001 2
01/10/2001 4
01/11/2001 3
01/12/2001 2
01/13/2001 4
01/14/2001 5
01/15/2001 5
01/16/2001 4
01/17/2001 4
To simplify, Here is the Cursor for *Rolling Five Days*.
RollingDATE Rolling5DayCOUNT
01/08/2001
01/09/2001
01/10/2001
01/11/2001
01/12/2001 12
01/13/2001 15
01/14/2001 16
01/15/2001 18
01/16/2001 17
01/17/2001 17
How to I program this:
Here is a table will all the columns for:
* Raw Data
* Count per day
* Count per 5 days
DATE TIME AMOUNT DayCount Rolling5DayCOUNT
01/08/2001 6:21 pm 130 1
01/09/2001 10:46 pm 148
01/09/2001 6:08 pm 116 2
01/10/2001 11:07 pm 148
01/10/2001 6:18 pm 138
01/10/2001 1:11 pm 122
01/10/2001 7:31 am 135 4
01/11/2001 9:53 pm 141
01/11/2001 5:56 pm 167
01/11/2001 7:36 am 141 3
01/12/2001 6:55 pm 131
01/12/2001 8:02 am 156 2 12
01/13/2001 6:21 pm 111
01/13/2001 12:16 pm 138
01/13/2001 8:34 am 175
01/13/2001 1:50 am 189 4 16
01/14/2001 6:07 pm 109
01/14/2001 11:58 am 95
01/14/2001 11:51 am 149
01/14/2001 8:06 am 151
01/14/2001 12:06 am 144 5 16
01/15/2001 11:28 pm 143
01/15/2001 6:18 pm 103
01/15/2001 12:04 pm 90
01/15/2001 7:44 am 116
01/15/2001 2:33 am 160 5 18
01/16/2001 11:28 pm 156
01/16/2001 11:04 pm 126
01/16/2001 6:01 pm 156
01/16/2001 7:57 am 156 4 17
01/17/2001 10:18 pm 111
01/17/2001 6:13 pm 126
01/17/2001 1:11 pm 158
01/17/2001 8:01 am 142 4 17
-- *Charles* --
Website: http://homepages.uc.edu/~enzerch/
<http://homepages.uc.edu/%7Eenzerch/>
*"Medicine is a science of uncertainty and an art of probability"
*William Osler, M.D.
***Stop **Spammers**and **Virus Propagation***
***Before **forwarding**, please **delete**the history of all email
address***
On 5/1/2013 5:52 PM, Frank Cazabon wrote:
Charles,
Could you write a program which creates a cursor with some sample
data and post it here and let us know what the expected output is
and I'm sure one of us will be able to give you the working code
"Charles Hart Enzer, M.D., FAACP" <[email protected]> wrote:
Dear Frank:
Thank you.
The SELECT also gives the [Unrocognized Command] error:
*SELECT Gluctmp1.date, SELECT COUNT(Gluctmp1.amount) FROM
GlucTmp1
GlucTmp2 WHERE GlucTmp2.Date BETWEEN GlucTmp1.Date AND
GlucTmp1.Date
+ 20); **
** FROM Gluctmp1; **
** WHERE ; **
** Gluctmp1.category = "Glucos"; **
** GROUP BY Gluctmp1.date
*
So I changed it to:
*SELECT ;
Gluctmp1.date AS Rollingdate, ;
COUNT(Gluctmp1.amount) AS Totals ;
FROM Gluctmp1;
WHERE ;
Gluctmp1.category = "Glucos";
AND Gluctmp1.amount in ;
(SELECT ;
COUNT(Gluctmp1.amount) ;
FROM GlucTmp1 ;
WHERE GlucTmp1.Date BETWEEN GlucTmp1.Date ;
AND GlucTmp1.Date + 20 );
GROUP BY Gluctmp1.date
*
And got an empty Cursor.
-- *Charles* --
Website: http://homepages.uc.edu/~enzerch/
<http://homepages.uc.edu/%7Eenzerch/>
*"Medicine is a science of uncertainty and an art of probability"
*William Osler, M.D.
***Stop **Spammers**and **Virus Propagation***
***Before **forwarding**, please **delete**the history of all email
address***
On 5/1/2013 5:09 PM, Frank Cazabon wrote:
SELECT Gluctmp1.date, SELECT COUNT(Gluctmp1.amount) FROM GlucTmp1
GlucTmp2 WHERE GlucTmp2.Date BETWEEN GlucTmp1.Date AND GlucTmp1.Date
+
20);
FROM Gluctmp1;
WHERE ;
Gluctmp1.category = "Glucos";
GROUP BY Gluctmp1.date
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
