Average Computation Question

[Alex]

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.

[Alex]

hf*****@yahoo.com (Alex) wrote in message news:<c5**************************@posting.google. 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.

[Dan Guzman]

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 GetAverageMinutes
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.google.c om...

hf*****@yahoo.com (Alex) wrote in message
news:<c5**************************@posting.google. 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.

[Joe Celko]

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!

[Alex]

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******@nospam-online.sbcglobal.net> wrote in message news:<Ze***************@newssvr30.news.prodigy.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 GetAverageMinutes
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.google.c om...

hf*****@yahoo.com (Alex) wrote in message
news:<c5**************************@posting.google. 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.

[Gert-Jan Strik]

Try this:

SELECT AVG(Duration)
FROM (
SELECT DATEDIFF(minute,MIN(MyTimeStap),MAX(MyTimeStamp)) 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******@nospam-online.sbcglobal.net> wrote in message news:<Ze***************@newssvr30.news.prodigy.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 GetAverageMinutes
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.google.c om...

hf*****@yahoo.com (Alex) wrote in message
news:<c5**************************@posting.google. 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)

[Alex]

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***@toomuchspamalready.nl> wrote in message news:<41***************@toomuchspamalready.nl>...

Try this:

SELECT AVG(Duration)
FROM (
SELECT DATEDIFF(minute,MIN(MyTimeStap),MAX(MyTimeStamp)) 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******@nospam-online.sbcglobal.net> wrote in message news:<Ze***************@newssvr30.news.prodigy.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 GetAverageMinutes
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.google.c om...
> hf*****@yahoo.com (Alex) wrote in message
> news:<c5**************************@posting.google. 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.

[Alex]

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*******@earthlink.net> wrote in message news:<41**********************@news.newsgroups.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!

[Joe Celko]

>> 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!