Query Help!

Question

Query Help!

woody_rd

Ten Centuries

Points: 1012
More actions
February 21, 2008 at 10:42 am

#75854

I need some advice on a query I need to write to get some information from a table....
Basically I have a events that are written to a table and I need to know how many times a certain series of events occurs. So I have the following pieces of data:
Userid, event, start, stop
I have a series of specific events as follows:
event 1, event 2, event 9, event 10
I want to know how many times that series happens per userid, but not really sure how to go about querying it.
Any suggestions are greatly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please can you post the table structure, some sample data and the expected results. It makes it much easier for us to answer your question.

Ref: http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

woody_rd Ten Centuries Points: 1012 More actions · Answer 2

Here's the table structure:

event_id bigint (unique identifier)

userid varchar(36) (unique user id)

event varchar(50) (description of the event)

processed_date datetime (date/time of the event)

Sample Data:

1543, 191E32AE-AEE2-4B68-8026-9FA8CF866CF1, Logged On, 1/1/2008 09:00:00

1544, 191E32AE-AEE2-4B68-8026-9FA8CF866CF1, Start Event, 1/1/2008 10:15:00

1545, 191E32AE-AEE2-4B68-8026-9FA8CF866CF1, Clicked Start OK, 1/1/2008 10:15:04

1546, 191E32AE-AEE2-4B68-8026-9FA8CF866CF1, Stop Event, 1/1/2008 10:30:00

1547, 191E32AE-AEE2-4B68-8026-9FA8CF866CF1, Clicked Stop OK, 1/1/2008 10:30:01

1548, 5692771B-F77B-436E-A123-F56328D2518E, Logged On, 1/1/2008 10:40:00

1549, 5692771B-F77B-436E-A123-F56328D2518E, Start Event, 1/1/2008 10:45:00

1550, 5692771B-F77B-436E-A123-F56328D2518E, Clicked Start OK, 1/1/2008 10:45:09

1551, 5692771B-F77B-436E-A123-F56328D2518E, Opened Page, 1/1/2008 10:45:13

1552, 5692771B-F77B-436E-A123-F56328D2518E, Closed Page, 1/1/2008 10:49:44

1553, 5692771B-F77B-436E-A123-F56328D2518E, Went Passive, 1/1/2008 10:55:10

1554, 5692771B-F77B-436E-A123-F56328D2518E, Stop Event, 1/1/2008 11:00:00

1555, 5692771B-F77B-436E-A123-F56328D2518E, Clicked Stop OK, 1/1/2008 11:00:01

in this scenario, what I want to capture is when I get a sequence of events like 1544 - 1547 where there was no other activity than start - click ok - stop - click ok. Desire output would be as follows:

191E32AE-AEE2-4B68-8026-9FA8CF866CF1, 1 (basically this user had 1 sequence that matched the pattern)

Please let me know if you require any additional information.

Again, your help is greatly appreciated.

-Woody