473,545 Members | 1,773 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Average Computation Question

My table is laid out as such:

ID (int) What (varchar 20) TimeStamp (smalldatetime)
------- ------------- ---------------
73 Start <T1>
73 Misc <T2>
73 End <T3>
81 Start <T1'>
81 Misc <T2'>
81 End <T3'>
....

I need to calculate End - Start for each unique ID (i.e. T3-T1 and
T3'-T1') and then take the average of those (2 in this case) entries.

Any help is appreciated.

Alex.
Jul 20 '05 #1
8 1688
hf*****@yahoo.c om (Alex) wrote in message news:<c5******* *************** ****@posting.go ogle.com>...
My table is laid out as such:

ID (int) What (varchar 20) TimeStamp (smalldatetime)
------- ------------- ---------------
73 Start <T1>
73 Misc <T2>
73 End <T3>
81 Start <T1'>
81 Misc <T2'>
81 End <T3'>
...

I need to calculate End - Start for each unique ID (i.e. T3-T1 and
T3'-T1') and then take the average of those (2 in this case) entries.

Any help is appreciated.

Alex.


Ps: I am running SQL 2000 SP3 and am looking for the stored procedure
code that'll accomplish the above.
Jul 20 '05 #2
Try something like:

CREATE TABLE MyTable
(
ID int NOT NULL,
What varchar(5) NOT NULL,
MyTimeStamp smalldatetime NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY (ID, What)
)
GO

INSERT INTO MyTable VALUES(73, 'Start', '20040901')
INSERT INTO MyTable VALUES(73, 'Misc', '20040905')
INSERT INTO MyTable VALUES(73, 'End', '20040909')
INSERT INTO MyTable VALUES(81, 'Start', '20040915')
INSERT INTO MyTable VALUES(81, 'Misc', '20040917')
INSERT INTO MyTable VALUES(81, 'End', '20040919')
GO

CREATE PROCEDURE GetAverageMinut es
AS
SELECT
AVG(DATEDIFF(mi , a.MyTimeStamp, b.MyTimeStamp)) AS AverageMinutes
FROM MyTable a
JOIN MyTable b ON
b.ID = a.ID AND
a.What = 'Start' AND
b.What = 'End'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Alex" <hf*****@yahoo. com> wrote in message
news:c5******** *************** ***@posting.goo gle.com...
hf*****@yahoo.c om (Alex) wrote in message
news:<c5******* *************** ****@posting.go ogle.com>...
My table is laid out as such:

ID (int) What (varchar 20) TimeStamp (smalldatetime)
------- ------------- ---------------
73 Start <T1>
73 Misc <T2>
73 End <T3>
81 Start <T1'>
81 Misc <T2'>
81 End <T3'>
...

I need to calculate End - Start for each unique ID (i.e. T3-T1 and
T3'-T1') and then take the average of those (2 in this case) entries.

Any help is appreciated.

Alex.


Ps: I am running SQL 2000 SP3 and am looking for the stored procedure
code that'll accomplish the above.

Jul 20 '05 #3
Your design is fundamentally wrong. The flaw is called "attribute
splitting" and you can Google it. Time comes in durations and not
points (see Einstein and Zeno for the details). The DDL that you did
not post should have looked more like this:

CREATE TABLE Foobar
(event_id INTEGRR NOT NULL PRIMARY KEY,
event-description VARCHAR(20) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL,
CHECK (start_time < end_time));
I need to calculate End - Start for each unique ID (i.e. T3-T1 and

T3'-T1') and then take the average of those (2 in this case) entries. <<

Since you used Standard SQL TIMESTAMP in your pseudo-code, here is the
trivial answer:

SELECT AVG(INTERVAL (end_time - start_time) SECONDS)
FROM Foobar;

A proper design saves orders of magnitude in the queries.

Your problem is that: (1) you do not understand time; no great shame
there, since most people get it messed up (2) You designed a table to
mimick a paper form, namely the list you used for keeping track of
things. Think more abstractly; one attribute can be split in many
fields on the non-relational side, but must bre put into one and only
one column when it gets to the database.

The other answers you get will be fancy self-joins that bring the
durations make from the attribute split.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4
Dan, that's beautiful. Thanks. I have a follow up question:

If the "End" time stamp were not unique, meaning that the "End" time
stamp could occur multiple times and I had to take the last one for
the purposes of the average computation what would the SQL look like
then?

Thanks again.

Alex.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message news:<Ze******* ********@newssv r30.news.prodig y.com>...
Try something like:

CREATE TABLE MyTable
(
ID int NOT NULL,
What varchar(5) NOT NULL,
MyTimeStamp smalldatetime NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY (ID, What)
)
GO

INSERT INTO MyTable VALUES(73, 'Start', '20040901')
INSERT INTO MyTable VALUES(73, 'Misc', '20040905')
INSERT INTO MyTable VALUES(73, 'End', '20040909')
INSERT INTO MyTable VALUES(81, 'Start', '20040915')
INSERT INTO MyTable VALUES(81, 'Misc', '20040917')
INSERT INTO MyTable VALUES(81, 'End', '20040919')
GO

CREATE PROCEDURE GetAverageMinut es
AS
SELECT
AVG(DATEDIFF(mi , a.MyTimeStamp, b.MyTimeStamp)) AS AverageMinutes
FROM MyTable a
JOIN MyTable b ON
b.ID = a.ID AND
a.What = 'Start' AND
b.What = 'End'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Alex" <hf*****@yahoo. com> wrote in message
news:c5******** *************** ***@posting.goo gle.com...
hf*****@yahoo.c om (Alex) wrote in message
news:<c5******* *************** ****@posting.go ogle.com>...
My table is laid out as such:

ID (int) What (varchar 20) TimeStamp (smalldatetime)
------- ------------- ---------------
73 Start <T1>
73 Misc <T2>
73 End <T3>
81 Start <T1'>
81 Misc <T2'>
81 End <T3'>
...

I need to calculate End - Start for each unique ID (i.e. T3-T1 and
T3'-T1') and then take the average of those (2 in this case) entries.

Any help is appreciated.

Alex.


Ps: I am running SQL 2000 SP3 and am looking for the stored procedure
code that'll accomplish the above.

Jul 20 '05 #5
Try this:

SELECT AVG(Duration)
FROM (
SELECT DATEDIFF(minute ,MIN(MyTimeStap ),MAX(MyTimeSta mp)) AS Duration
FROM MyTable
GROUP BY ID
) AS T1

By the way: this is not a homework assignment, is it?

Gert-Jan

Alex wrote:

Dan, that's beautiful. Thanks. I have a follow up question:

If the "End" time stamp were not unique, meaning that the "End" time
stamp could occur multiple times and I had to take the last one for
the purposes of the average computation what would the SQL look like
then?

Thanks again.

Alex.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message news:<Ze******* ********@newssv r30.news.prodig y.com>...
Try something like:

CREATE TABLE MyTable
(
ID int NOT NULL,
What varchar(5) NOT NULL,
MyTimeStamp smalldatetime NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY (ID, What)
)
GO

INSERT INTO MyTable VALUES(73, 'Start', '20040901')
INSERT INTO MyTable VALUES(73, 'Misc', '20040905')
INSERT INTO MyTable VALUES(73, 'End', '20040909')
INSERT INTO MyTable VALUES(81, 'Start', '20040915')
INSERT INTO MyTable VALUES(81, 'Misc', '20040917')
INSERT INTO MyTable VALUES(81, 'End', '20040919')
GO

CREATE PROCEDURE GetAverageMinut es
AS
SELECT
AVG(DATEDIFF(mi , a.MyTimeStamp, b.MyTimeStamp)) AS AverageMinutes
FROM MyTable a
JOIN MyTable b ON
b.ID = a.ID AND
a.What = 'Start' AND
b.What = 'End'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Alex" <hf*****@yahoo. com> wrote in message
news:c5******** *************** ***@posting.goo gle.com...
hf*****@yahoo.c om (Alex) wrote in message
news:<c5******* *************** ****@posting.go ogle.com>...
> My table is laid out as such:
>
> ID (int) What (varchar 20) TimeStamp (smalldatetime)
> ------- ------------- ---------------
> 73 Start <T1>
> 73 Misc <T2>
> 73 End <T3>
> 81 Start <T1'>
> 81 Misc <T2'>
> 81 End <T3'>
> ...
>
> I need to calculate End - Start for each unique ID (i.e. T3-T1 and
> T3'-T1') and then take the average of those (2 in this case) entries.
>
> Any help is appreciated.
>
> Alex.

Ps: I am running SQL 2000 SP3 and am looking for the stored procedure
code that'll accomplish the above.


--
(Please reply only to the newsgroup)
Jul 20 '05 #6
Got it going. Thanks. The insight was really the self-join that Dan
mentioned. It was a minor tweak to get the rest working. Thanks for
all your help. And no it's not a homework assignment. :)

Gert-Jan Strik <so***@toomuchs pamalready.nl> wrote in message news:<41******* ********@toomuc hspamalready.nl >...
Try this:

SELECT AVG(Duration)
FROM (
SELECT DATEDIFF(minute ,MIN(MyTimeStap ),MAX(MyTimeSta mp)) AS Duration
FROM MyTable
GROUP BY ID
) AS T1

By the way: this is not a homework assignment, is it?

Gert-Jan

Alex wrote:

Dan, that's beautiful. Thanks. I have a follow up question:

If the "End" time stamp were not unique, meaning that the "End" time
stamp could occur multiple times and I had to take the last one for
the purposes of the average computation what would the SQL look like
then?

Thanks again.

Alex.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message news:<Ze******* ********@newssv r30.news.prodig y.com>...
Try something like:

CREATE TABLE MyTable
(
ID int NOT NULL,
What varchar(5) NOT NULL,
MyTimeStamp smalldatetime NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY (ID, What)
)
GO

INSERT INTO MyTable VALUES(73, 'Start', '20040901')
INSERT INTO MyTable VALUES(73, 'Misc', '20040905')
INSERT INTO MyTable VALUES(73, 'End', '20040909')
INSERT INTO MyTable VALUES(81, 'Start', '20040915')
INSERT INTO MyTable VALUES(81, 'Misc', '20040917')
INSERT INTO MyTable VALUES(81, 'End', '20040919')
GO

CREATE PROCEDURE GetAverageMinut es
AS
SELECT
AVG(DATEDIFF(mi , a.MyTimeStamp, b.MyTimeStamp)) AS AverageMinutes
FROM MyTable a
JOIN MyTable b ON
b.ID = a.ID AND
a.What = 'Start' AND
b.What = 'End'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Alex" <hf*****@yahoo. com> wrote in message
news:c5******** *************** ***@posting.goo gle.com...
> hf*****@yahoo.c om (Alex) wrote in message
> news:<c5******* *************** ****@posting.go ogle.com>...
>> My table is laid out as such:
>>
>> ID (int) What (varchar 20) TimeStamp (smalldatetime)
>> ------- ------------- ---------------
>> 73 Start <T1>
>> 73 Misc <T2>
>> 73 End <T3>
>> 81 Start <T1'>
>> 81 Misc <T2'>
>> 81 End <T3'>
>> ...
>>
>> I need to calculate End - Start for each unique ID (i.e. T3-T1 and
>> T3'-T1') and then take the average of those (2 in this case) entries.
>>
>> Any help is appreciated.
>>
>> Alex.
>
> Ps: I am running SQL 2000 SP3 and am looking for the stored procedure
> code that'll accomplish the above.

Jul 20 '05 #7
Joe, although you seem to have a good grasp of time and its nuances, I
am not sure if you tried to solve my problem or found it easier (i.e.
less time) to solve your own. But thanks for the brief time you
alloted to my post and replying. A.

Ps: you were right about the DDL. I should have posted one. Sorry
and will do better next time.

Joe Celko <jc*******@eart hlink.net> wrote in message news:<41******* *************** @news.newsgroup s.ws>...
Your design is fundamentally wrong. The flaw is called "attribute
splitting" and you can Google it. Time comes in durations and not
points (see Einstein and Zeno for the details). The DDL that you did
not post should have looked more like this:

CREATE TABLE Foobar
(event_id INTEGRR NOT NULL PRIMARY KEY,
event-description VARCHAR(20) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL,
CHECK (start_time < end_time));
I need to calculate End - Start for each unique ID (i.e. T3-T1 and

T3'-T1') and then take the average of those (2 in this case) entries. <<

Since you used Standard SQL TIMESTAMP in your pseudo-code, here is the
trivial answer:

SELECT AVG(INTERVAL (end_time - start_time) SECONDS)
FROM Foobar;

A proper design saves orders of magnitude in the queries.

Your problem is that: (1) you do not understand time; no great shame
there, since most people get it messed up (2) You designed a table to
mimick a paper form, namely the list you used for keeping track of
things. Think more abstractly; one attribute can be split in many
fields on the non-relational side, but must bre put into one and only
one column when it gets to the database.

The other answers you get will be fancy self-joins that bring the
durations make from the attribute split.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #8
>> Joe, although you seem to have a good grasp of time and its nuances,
<<

Lord, no! That guy is Rick Snodgrass. his book on temproal queries in
SQL is on-line at his website at the University of AZ. I can simply
recognize the most common basic problems in DDL by sight now; I make a
part of my living fixing databases that look like what you posted.
I am not sure if you tried to solve my problem or found it easier (i.e.less time) to solve your own. <<

I published this kind of solution in SQL FOR SMARTIES, SQL PUZZLES and
several magazine columns years ago when I was still thinking of time as
points and not durations. How else would I know that there would have
to be an elaborate and error-prone self-join in whatever kludge got
posted? :)

Your problem *is* the design and that is the root of the difficulty in
even this simple query. It will get orders of magntiude worse. The
elaborate self-joins eat up time exponentially with DB size. A single
missing row throws reports off. Gaps are hard to detect.

I know; I have been paid to fix it before at a research company working
with a bank to look for patterns in checking account and credit card
balances. Hiring me for a month is expensive :)
Ps: you were right about the DDL. I should have posted one. Sorry and

will do better next time. <<

Nada. The number of frequent posters who have been asked over and over
and still will not post DDL is remarkable. Then of course there are the
guys who push a button and dump code in a format that only a machine
could love ..

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3131
by: J | last post by:
Kind of new at programming/vb.net. I'm doing this junky die roller program. Heres's what is supposed to happen: Roll 2 6-sided dies. Add rolls together put total in rolls(d6total). Display the number of 2 or 12 rolled, then display those numbers and average rolls. I didn't know how to do it exactly, but I came up with this.
2
9514
by: johnywalkyra | last post by:
Hello, first of all sorry for crossposting, but I could not decide which group is more appropriate. To my question: Recently I've came across the code in GCC standard library, which computes the cube root of a real (floating point) number. Could anyone explain me the math behind the computation? Here's the snippet: ...
3
2305
by: C++Geek | last post by:
I need to get this program to average the salaries. What am I doing wrong? //Program to read in employee data and calculate the average salaries of the emplyees.
4
6313
by: sbowman | last post by:
I have a table with help desk ticketing information. There is a Date/Time open, Date/Time closed field both formatted as: MM/DD/YYYY hh:nn:ss I need to calculate the difference between these two times and then calculate the average of the difference. How do I go about this??? Help is much appreciated... Shelley
3
4132
by: mochatrpl | last post by:
I am looking for a way to make a query / report display the running average for total dollars. I have already set up a query to provide totals dollars per day from which a report graphly shows the dollars per week. How do I then take the dollars and get a running average for the year? - Randy
4
6529
by: gaga | last post by:
hi guys, a part of my program requires me to calculate an average of items that are sold. the easiest way to do that would be writing a function, but im having trouble making up the parameters. if you can point me which elements to use when performing such a task, i would really appreciate it. thanks
3
5295
by: Salad | last post by:
http://www.mathwords.com/w/weighted_average.htm At the above link gives an example of a weighted average. It uses the following example: Grades are often computed using a weighted average. Suppose that homework counts 10%, quizzes 20%, and tests 70%. If Pat has a homework grade of 92, a quiz grade of 68, and a test grade of 81, then
10
5909
by: vincex200 | last post by:
My group needs help with this program. We attempted to start it and got no where. Please help us. Write a C++ program that will read data from a file, perform computation on the data, then print the result to another file. The data from the file will contian 100 lines where each line contains three items. The three items are an arithmetic...
14
3119
by: Luna Moon | last post by:
Dear all, Can C++/STL/Boost do the vectorized calculation as those in Matlab? For example, in the following code, what I really want to do is to send in a vector of u's. All other parameters such as t, l1, l2, l3, etc. are scalars... But u is a vector.
0
7457
marktang
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...
0
7391
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7651
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
5962
agi2029
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5320
isladogs
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...
0
4941
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...
1
1869
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
1
1010
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
693
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.