Filter consecutive records

Filter consecutive records

Post by Doru Roma » Fri, 10 Oct 2003 20:05:56



Hi,

CREATE TABLE [dbo].[MyTable] (
 [Start_Date] [datetime] NOT NULL ,
 [FromS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ToS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ToS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Status] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Rec_ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

I try to update Status with value 'marked' for all less than 5 consecutive
records that have a
Start_Date within 3 minutes from one another from the same point FromS to
the same point ToS.
So consecutive means more than one occurrence but less than 5 of calls made
within 3 minutes.
All my attempts resulted in updating only the first 2 out of 7 consecutive
calls - as an example.
And that is because only the first two calls have 5 consecutive calls after
them. After the second
one is updated the third one does not have more than 5 consecutive calls
left.
But I want to update the whole group.

--

Thank you,
Doru

 
 
 

Filter consecutive records

Post by Alejandro Mes » Fri, 10 Oct 2003 20:20:04


Could you post some sample data and expected result?

Thanks,

AMB

 
 
 

Filter consecutive records

Post by Doru Roma » Fri, 10 Oct 2003 20:39:26


I noticed that I generated ToS twice. Only one instance is valid;
So here is the data:
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:23:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:24:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:26:15', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:26:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:26:55', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:27:15', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:27:22', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:27:45', 'D', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:31:15', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:31:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:32:15', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:32:23', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:38:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:38:47', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:39:25', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:41:45', 'A', 'B', NULL)
Insert Into MyTable()Start_date,FromS,ToS,Status Values ('10/6/2003
11:43:45', 'A', 'B', NULL)

After:
'10/6/2003 11:23:45', 'A', 'B', 'marked' ,1
'10/6/2003 11:24:45', 'A', 'B', 'marked' ,2
'10/6/2003 11:26:15', 'A', 'B', 'marked' ,3
'10/6/2003 11:26:45', 'A', 'B', 'marked' ,4
'10/6/2003 11:26:55', 'A', 'B', 'marked' ,5
'10/6/2003 11:27:15', 'A', 'B', 'marked' ,6
'10/6/2003 11:27:22', 'A', 'B', 'marked' ,7
'10/6/2003 11:27:45', 'D', 'B', NULL,8
'10/6/2003 11:31:15', 'A', 'B', NULL,9
'10/6/2003 11:31:45', 'A', 'B', NULL,10
'10/6/2003 11:32:15', 'A', 'B', NULL,11
'10/6/2003 11:32:23', 'A', 'B', NULL,12
'10/6/2003 11:38:45', 'A', 'B', NULL,13
'10/6/2003 11:38:47', 'A', 'B', NULL,14
'10/6/2003 11:39:25', 'A', 'B', NULL,15
'10/6/2003 11:41:45', 'A', 'B', NULL,16
'10/6/2003 11:43:45', 'A', 'B', NULL,17

--

Thank you,
Doru


Quote:> Could you post some sample data and expected result?

> Thanks,

> AMB

 
 
 

1. How to count consecutive numbers, like most consecutive wins from a results database

Hi,

I hopy you guys can help me out (and I hope I'm in the right newsgroup).

I have a problem to create a query (MySQL) which counts consecutive numbers.

Assume I have the following table:

ID date       uid  position
 1 01/01/2001 1001 1
 2 01/01/2001 1002 2
 3 01/01/2001 1003 3
 4 02/01/2001 1001 1
 5 02/01/2001 1003 2
 6 02/01/2001 1004 3
 7 03/01/2001 1001 1
 8 03/01/2001 1002 2
 9 03/01/2001 1003 3
10 04/01/2001 1002 1
11 04/01/2001 1001 2
12 04/01/2001 1006 3
13 05/01/2001 1001 1
14 05/01/2001 1003 2
15 05/01/2001 1004 3

I want to try e.g. :

Lookup the max number of consecutive wins per uid:
uid  consecutive_wins
1001 3
1002 1
1003 0
1004 0
1005 0
1006 0

Lookup the max number of consecutive top 2 positions per uid:
uid  consecutive_top2
1001 5
1002 2
1003 1
1004 0
1005 0
1006 0

Lookup the max number of consecutive top 3 positions per uid:
uid  consecutive_top3
1001 5
1003 3
1002 2
1004 1
1006 1
1005 0

The uid with a zero results do not have to be displayed, they are
in there to make the example more clearer.

I hope the above example is clear enough. I have a huge database
with Formula 1 results. I want to add some additional statistics
like:
- max nr of consecutive wins per driver
- max nr of consecutive top6 position per driver
- etc. etc.

The only solution I coult think of at this moment is a very resource
intensive one (Lookup all uid's, get all results for 1st uid(1), count
consecutive wins in results for that uid, get all results for next(2)
uid, count consecutive wins in results for that uid, get all results
for next(3) uid, etc. etc. etc. ).

I hope you can help me out with a better (and maybe less resource
intensive) method.

Thanks in advance, EvE

2. Phantom clients in MicroKernal Monitor

3. Looping through a dataset to calculate the amount of inactive time between consecutive records

4. VFP Picture Display Tools?

5. Filtering records / Manipulating multiple database records...

6. How to retrieve detailed info DB2 from Essbase

7. FILTER PORTAL RECORDS - DISABLE ADDING NEW RECORDS IN A PORTAL

8. CSS & XML

9. Filter records in a dimension

10. return of ADODB.Recordset.Filter if no record find

11. Viewing Database records after a filter

12. Joining filtering records / tables

13. Help filtering child records...