Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Statistical Analysis

 
   Database Help (Home) -> Datamining RSS
Next:  PredictProbability function  
Author Message
Ted Burhan

External


Since: Nov 06, 2003
Posts: 2



(Msg. 1) Posted: Thu Nov 06, 2003 1:47 pm
Post subject: Statistical Analysis
Archived from groups: microsoft>public>sqlserver>datamining (more info?)

I have an event table structured as follows:

Column Name Type
========== ====
Timestamp datetime
SourceID nvarchar
EventID nvarchar

There is no primary key in the table.
What I'm wanting to achieve is to get the time differences between pairs of
events (Event1 & Event2).
So for example the records

01/01/2001 1:00:00 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2

would give me the result 3600 seconds. If the event does not have a pair,
then it would be ignored.
For example the records

01/01/2001 1:00:00 PM Source1 Event1
01/01/2001 1:00:05 PM Source1 Event1
01/01/2001 1:00:10 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2
01/01/2001 2:00:01 PM Source1 Event2
01/01/2001 2:00:02 PM Source1 Event2
01/01/2001 2:00:03 PM Source1 Event1

would yield only one elligible pair, which is

01/01/2001 1:00:10 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2

hence, the time difference = 3610 seconds

here's the query I came up with, but its really slow.. taking about 2 1/2
minutes to look through 20005 records to find 10000 pairs

SELECT DATEDIFF(ss, Timestamp1, MIN(Timestamp2)) AS secs
FROM (SELECT b.Timestamp AS Timestamp2, MAX(a.Timestamp) AS Timestamp1
FROM tblEvents a INNER JOIN (SELECT *
FROM
tblEvents
WHERE
EventID = 'Event2') b
ON a.ProcessorID = b.ProcessorID
AND a.Timestamp < b.Timestamp
WHERE a.EventID = 'Event1'
GROUP BY b.Timestamp) derivedtbl
GROUP BY Timestamp1

I was wondering if anyone could suggest me a better query. I'm a newbie, so
if somebody could help me I would greatly appreciate it.

Regards,
Ted

 >> Stay informed about: Statistical Analysis 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Analysis Services Algorithms - Can I buy Algorithms for MSAS from a third party company? Can you suggest any?

Analysis services vs PMML - what are the advantages of using PMML for creating data mining structures instead of the Analysis services GUI? /Dennis

analysis services & clr - Hi, I have some problems deploying my cube. I have made two CLR udts called : trajectory, rectangle and the corresponding database tables. I have also created a fact table with the following columns : rectangle_id : dimension trajectory_id: dimension..

Embedding Intelligence Throughout Your Marketing Organizat.. - http://www.inforsense.com/index.php?id=166 Event: Embedding Intelligence Throughout Your Marketing Organization: New Techniques for Marketing Data Analysis Presenter(s) Info: Michael Berry, Data Miners, Inc Anthony Rowe, Product Manager, InforSense..

Collation Problem any help - Hello all : My problem is like the following I have an online databse with a latin collation, this database is using ntext and nvarchar values that was with our previous admin ok the problem is in the data is stored should use another collation (the...
   Database Help (Home) -> Datamining All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]