i've got a table with the datetimes that certain events happened, like
so:
create table events (
when datetime,
what varchar(50))
and i'm looking to do some calculations of this data. for example,
there are pairs of events A and B, and i'd like to get the min, max,
and average datediffs between them. like so:
insert into events values (getdate(), 'event A')
insert into events values (dateadd(ss, 1, getdate), 'event B')
insert into events values (getdate(), 'event A')
insert into events values (dateadd(ss, 2, getdate), 'event B')
insert into events values (getdate(), 'event A')
insert into events values (dateadd(ss, 3, getdate), 'event B')
the min difference between event A and B would be 1, the max would be
3, and the average would be 2. i think from that query, i would
probably be able to extrapolate any of the other data i might want to
crunch.
thanks for any help!
jason"jason" <iaesun@.yahoo.com> wrote in message
news:1137447878.831728.57480@.g14g2000cwa.googlegroups.com...
> i've got a table with the datetimes that certain events happened, like
> so:
> create table events (
> when datetime,
> what varchar(50))
> and i'm looking to do some calculations of this data. for example,
> there are pairs of events A and B, and i'd like to get the min, max,
> and average datediffs between them. like so:
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 1, getdate), 'event B')
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 2, getdate), 'event B')
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 3, getdate), 'event B')
> the min difference between event A and B would be 1, the max would be
> 3, and the average would be 2. i think from that query, i would
> probably be able to extrapolate any of the other data i might want to
> crunch.
> thanks for any help!
> jason
>
Thanks for the DDL but you left out the primary key. I'll assume it should
be [when] (a reserved word isn't a good name for a column BTW).
SQL Server 2005:
WITH T (a_when, a_what, b_when)
AS
(SELECT A.[when], A.what,
MIN(B.[when])
FROM events AS A
JOIN events AS B
ON A.[when] < B.[when]
AND A.what = 'event a'
AND B.what = 'event b'
GROUP BY A.[when], A.what)
SELECT
MIN(DATEDIFF(SECOND,a_when,b_when)) AS [min],
MAX(DATEDIFF(SECOND,a_when,b_when)) AS [max],
AVG(DATEDIFF(SECOND,a_when,b_when)) AS [avg]
FROM T;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jason,
I think you are going to have to elaborate a bit on this issue...
First, how do you intend to compare the dates below? I dont mean in a SQL
sense, but in a functional sense. As it is I can only guess at what you
intend to do. Let me know if I have it right, and give as much of a
detailed example as possible if I dont.
In the example below you have 6 rows of data. For the sake of discussion,
lets say they have values that differe by one day.
Jan-01-2005 Event A
Jan-02-2005 Event B (difference of 1 day)
Jan-01-2005 Event A
Jan-03-2005 Event B (difference of 2 days)
Jan-01-2005 Event A
Jan-04-2005 Event B (difference of 3 days)
Now, you have 3 entries for Event A, and 3 entries for event B. I believe
you are attempting to compare the "first" occurance of Event A with the
"first" occurance of event B, then the second occurance of each, and the
third, etc. If this is the case, you first want to have a key which will
identify the order of these occurences (or tie them together). Simply going
by the order of data entry, which it appears that you are, will get you into
all sorts of trouble.
Also, what if you have 4 entries for Event A and 3 for Event B, or vis
versa?
If you will always have a matching pair of events, and they will be tied
together by a key (lets call it EventLink). Then you would join the table
to itself, selecting "Event A" from one instance and "Event B" from the
other. You would select the difference between these two values, then take
the max, min, and average of that difference.
Mind you, this assumes:
that you know which two "what" you are going to compare.
that you have a key which will link one occurance of "what" to another
Also, dont trust my date math, it is strictly logical. You will have to
look up how to find the difference between two dates, I have yet to do it
with SQL Server.
i.e.
Select eventA.what
, eventB.what
, min(eventA.when-eventB.when)
, max(eventA.when-eventB.when)
, avg(eventA.when-eventB.when)
from events as eventA
inner join events as EventB
on eventA.EventLink = eventB.EventLink
where eventA.what = "Event A"
and eventB.what = "Event B"
"jason" <iaesun@.yahoo.com> wrote in message
news:1137447878.831728.57480@.g14g2000cwa.googlegroups.com...
> i've got a table with the datetimes that certain events happened, like
> so:
> create table events (
> when datetime,
> what varchar(50))
> and i'm looking to do some calculations of this data. for example,
> there are pairs of events A and B, and i'd like to get the min, max,
> and average datediffs between them. like so:
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 1, getdate), 'event B')
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 2, getdate), 'event B')
> insert into events values (getdate(), 'event A')
> insert into events values (dateadd(ss, 3, getdate), 'event B')
> the min difference between event A and B would be 1, the max would be
> 3, and the average would be 2. i think from that query, i would
> probably be able to extrapolate any of the other data i might want to
> crunch.
> thanks for any help!
> jason
>|||ahhh, sorry about that. this is actually a very, very temporary table
imported from a tab delimited log file, so it really doesn't have a
primary key, though i'm sure both columns could combine to be one.
also, still using SQL Server 2000, which i should have mentioned, which
doesn't seem to like the 'WITH' syntax. thanks very much though,
jason|||jim, this was perfect. there is no integral relational link between
events
ly, but applying the join logic that david portas mentioned
above (plus the date math, group by) made your query work like a charm.
thanks a bunch!|||jason (iaesun@.yahoo.com) writes:
> ahhh, sorry about that. this is actually a very, very temporary table
> imported from a tab delimited log file, so it really doesn't have a
> primary key, though i'm sure both columns could combine to be one.
> also, still using SQL Server 2000, which i should have mentioned, which
> doesn't seem to like the 'WITH' syntax. thanks very much though,
Here's a version of David's query that runs on SQL 2000.
SELECT
MIN(DATEDIFF(SECOND,a_when,b_when)) AS [min],
MAX(DATEDIFF(SECOND,a_when,b_when)) AS [max],
AVG(DATEDIFF(SECOND,a_when,b_when)) AS [avg]
FROM (SELECT a_when = A.[when], a_what = A.what,
b_when = MIN(B.[when])
FROM events AS A
JOIN events AS B ON A.[when] < B.[when]
AND A.what = 'event a'
AND B.what = 'event b'
GROUP BY A.[when], A.what) AS T
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your design is wrong. No keys, all NULLs and the wrong temporal model.
CREATE TABLE Events
(event_id CHAR(10) NOT NULL PRIMARY KEY,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means on-going
CHECK (start_time < end_time),
.);
time is a continuum, not a set of discrete points (see Zeno and
Einstein for details). Also, start using CURRENT_TIMESTAMP instead of
the proprietary getdate(); it makes you look like a real SQL programmer
:)|||haha, good ol' celko. it's not a 'design' sir, it's an imported table
form an operating system log file :)|||On 16 Jan 2006 18:08:53 -0800, --CELKO-- wrote:
>Your design is wrong. No keys, all NULLs and the wrong temporal model.
>CREATE TABLE Events
>(event_id CHAR(10) NOT NULL PRIMARY KEY,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means on-going
> CHECK (start_time < end_time),
> ..);
>time is a continuum, not a set of discrete points (see Zeno and
>Einstein for details).
Hi Joe,
And yet, events still happen at discrete points in that continuum. Same
as with distance and location - space is a continuum, but I am currently
at one single place.
Please tell me the start time and the end time of the following events
that, according to Wikipedia, took place on January 17:
1917 - The United States pays Denmark $25 million for the Virgin
Islands.
1929 - Popeye the Sailor Man, a cartoon character created by Elzie
Crisler Segar, first appeared in a newspaper comic strip.
1966 - Simon and Garfunkel release their second album, Sounds of
Silence, on Columbia Records.
1985 - British Telecom announces the retirement of Britain's famous red
telephone boxes.
1996 - The Czech Republic applies for membership of the European Union.
2006 - Clarence Ray Allen becomes the 2nd oldest condemned inmate
executed in the United States (oldest in California).
Hugo Kornelis, SQL Server MVP|||Jason,
Glad to hear you got this working. Could you post the final query? I'm
sure someone will come across this and benefit from seeing the complete
code.
"jason" <iaesun@.yahoo.com> wrote in message
news:1137451630.024792.89570@.g43g2000cwa.googlegroups.com...
> jim, this was perfect. there is no integral relational link between
> events
ly, but applying the join logic that david portas mentioned
> above (plus the date math, group by) made your query work like a charm.
> thanks a bunch!
>
