Multiple records searching (Sequence)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple records searching (Sequence)

Author

Topic

ChrisUK
Starting Member

2 Posts

Posted - 2003-08-18 : 09:17:47

Hi all,

I have a SQL 2000 table constructed as follows:

id dtmTime intChannel bitAction
3257 06/02/2002 12:00:00.000 5 1
3258 06/02/2002 12:00:01.250 18 1
3259 06/02/2002 12:00:02.000 4 0
3260 06/02/2002 12:00:03.500 16 0
etc..

Where:

id = identity
dtmTime = date and time (Not necessarily chronological)
intChannel = Channel between 1 and 99
bitAction = bit representing boolean value 1 or 0

Now the tricky part. I need to perform sequence filtering across the records based on channel and action.

For example, find any occurrences within the data where the following records are grouped together (a sequence):
Channel Action
18 true [start of sequence]
4 false [where record occurs directly after start record]
16 false [where record occurs directly after previous sequence record]
..etc
22 true [end of sequence]

In a typical scenario, I have got 10 sequences of varying lengths to find in 500,000 records. I have constructing a simple stored procedure that utilises fetch, but this is awfully slow.

Has anyone got any ideas?

Many thanks in advance.

Chris

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-18 : 09:30:06

Sounds quite involved. I might be tempted to extract possible sequences into another table first. You could do this by selecting from your table joined to itself on ID all the records from an ID with a channel that marks a possible start of a sequence to the ID + the maximum number of records in a sequence. That gives you a smaller data set to work with. Or something.

-------
Moo. :)

ToddV
Posting Yak Master

218 Posts

Posted - 2003-08-18 : 10:52:22

I am not really certain of what you are going for exactly, but I think this will point you in the right direction.

Create TABLE #Temp (
ID INT,
dtmTime DATETIME,
intChannel INT,
bitAction BIT)

INSERT #Temp
VALUES (3257, '06/02/2002 12:00:00.000' , 5, 1)
INSERT #Temp
VALUES (3258 , '06/02/2002 12:00:01.250' , 18, 1)
INSERT #Temp
VALUES (3259 , '06/02/2002 12:00:02.000' , 4, 0)
INSERT #Temp
VALUES (3260 , '06/02/2002 12:00:03.500' , 16, 0)
INSERT #Temp
VALUES (3260 , '06/02/2002 12:00:04.500' , 22, 1)

--Find Bounds for Each sequence
SELECT Identity(int,1,1) AS "ChainNbr",
dtmTime,
(SELECT TOP 1 dtmTime FROM #TEMP WHERE dtmTime > A.dtmTime and bitAction =1 Order by dtmTime ) AS "EndTime"
INTO #TEMP2
FROM #TEMP A
Where BitAction = 1

--Get the Chains
SELECT ChainNbr,
A.dtmTime,
intChannel,
bitAction
FROM #TEMP A
JOIN #TEMP2 B ON A.dtmTime >= B.dtmTime AND A.dtmTime < ISNULL(B.EndTime,GETDATE())

ChrisUK
Starting Member

2 Posts

Posted - 2003-08-18 : 11:24:20

ToddV - Thanks for your thorough response.

Unfortunately this is not what I am trying to achieve.

The date / time field isn't really a factor. The main objective is to locate (and flag) sequences of records.

Here's a real world scenario..

Imagine an office monitoring system (ACME monitoring system) where:

Channel 1 = door ; blnAction = open / close
Channel 2 = office light; blnAction = on/off
Channel 3 = workstation; blnAction = logon/off
Channel 4 = desk fan; blnAction = on / off
Channel 5 = window; blnAction = open / close

The database records the following:
(guy walks in, switches on light, logs on, logs off, switches off light, walks out)

Channel / Action
1 true
1 false
2 true
2 false
3 true
3 false
2 true
2 false
1 true
1 false

Now consider the following:

(guy walks in, switches on light, logs on, DOESN'T log off, switches off light, walks out)
1 true
1 false
2 true
2 false
3 true
2 true
2 false
1 true
1 false

We identify that this has occured, and want to search our db to ascertain whether this exact chain of events has occured before.

..so we search for occurrences of the above pattern.

Before you start asking why I want to do this, the above is only a simple illustration of what I am trying to do, and I must consider the entire sequence.

ToddV
Posting Yak Master

218 Posts

Posted - 2003-08-18 : 13:20:33

Ok I have another shot prepared. I bet if you capture the right guys attention you might get something better. But maybe this route will help out. (Always a disclaimer form me...lol)

Create TABLE #Temp (
ID INT,
dtmTime DATETIME,
intChannel INT,
bitAction BIT)

INSERT #Temp
VALUES (3257, '06/02/2002 12:00:00.000' , 5, 1)
INSERT #Temp
VALUES (3258 , '06/02/2002 12:00:01.250' , 18, 1)
INSERT #Temp
VALUES (3259 , '06/02/2002 12:00:02.000' , 4, 0)
INSERT #Temp
VALUES (3260 , '06/02/2002 12:00:03.500' , 16, 0)
INSERT #Temp
VALUES (3261 , '06/02/2002 12:00:04.500' , 22, 1)
INSERT #Temp
VALUES (3262 , '06/02/2002 12:00:05.500' , 16, 0)
INSERT #Temp
VALUES (3263 , '06/02/2002 12:00:01.250' , 18, 1)
INSERT #Temp
VALUES (3264 , '06/02/2002 12:00:02.000' , 4, 0)
INSERT #Temp
VALUES (3265 , '06/02/2002 12:00:05.500' , 16, 1)

DROP TABLE #TEMP

--Drop your sequence in a table
CREATE TABLE #Sequence (
ID INT,
intChannel INT,
bitAction BIT )

INSERT #Sequence
VALUES (1,18,1)
INSERT #Sequence
VALUES (2,4,0)
INSERT #Sequence
VALUES (3,16,0)

--Possible Occurences of the sequence
SELECT A.ID AS "Head"
INTO #TEMP2
FROM #Temp A
JOIN #Sequence B ON A.intChannel = B.intChannel and A.bitAction = B.bitAction
WHERE B.ID = 1

--This assumes that The Ids are in the order of occurence and no gaps exist
--If not you can reorder them and number them on the fly easily enough
SELECT Head
FROM #TEMP A
JOIN #Sequence B
ON A.intChannel = B.intChannel AND
A.bitAction = B.BitACtion
CROSS JOIN #Temp2 C
WHERE A.ID - C.Head = B.ID -1
GROUP BY Head
HAVING COUNT(*) = (SELECT COUNT(*) FROM #Sequence)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-19 : 10:33:28

check out my article about detecting "runs' and "streaks" in your data; i think it should help provide some ideas.

Just do a search in the articles page of SQLteam ... it might even still be on the main page as soon as you log into sqlteam.com.

let me know if it helps or not, or if you need more ideas...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources