Stored Procedure which skips rows in Select (Cursor?) 1

Rondrian · Sep 15, 2006

hi,
i have many troubles with a stored procedure which should select lines that have 3 or more times the same result value.

the table is a logfile table and the structure coulnd't be edited by me:

Code:

TIME  NAME	VALUE
0000  Smith     100
0013  Josh      100
0014  Smith     0
0320  Smith     0
0440  Josh      100
0510  Ohara     100
0615  Smith     100
0705  Ohara     0
0806  Ohara     0
0913  Smith     0
1014  Josh      100
1115  Ohara     0
1245  Josh      100
1320  Smith     0
1350  Ohara     0
1410  Smith     100
1420  Ohara     100
1430  Ohara     0
...

so, now i want to filter out all rows on which one person have consecutively 3 or more "0" in table, so the result should be:

Code:

TIME  NAME    VALUE
0705  Ohara   0
0806  Ohara   0  
1115  Ohara   0
1350  Ohara   0

do i need cursors for that or temp tables to do it?

many thanks for your help and sorry for my poor english

cya
andy

vbkris · Sep 15, 2006

normal SQL ought to solve this, try this:

select TIME,NAME,count(VALUE) from table
group by TIME,NAME
having count(VALUE)>2

Known is handfull, Unknown is worldfull

vbkris · Sep 15, 2006

ah, sorry, try this:

select * from Table
inner join
(
select NAME,count(VALUE) from table
group by NAME
having count(VALUE)>2
) DuplicateTable
on
Table.Name=DuplicateTable.Name

pls, let me know if that works...

Known is handfull, Unknown is worldfull

Rondrian · Sep 15, 2006

thx for your fast answer, but your query won't work:

original table:

Code:

time	                Name	                Value
15.09.2006 01:10:00	Win-RT_Kreda_15561	0
15.09.2006 01:11:00	Win-RT_Kreda_13725	100
15.09.2006 01:20:00	Win-RT_Ebila_15561	0
15.09.2006 01:40:00	Win-RT_Kreda_15561	0
15.09.2006 01:45:00	Win-RT_Kreda_13725	100
15.09.2006 02:10:00	Win-RT_Ebila_15561	100
15.09.2006 02:12:00	Win-RT_Kreda_15561	0
15.09.2006 02:50:00	Win-RT_Kreda_13725	0
15.09.2006 02:55:00	Win-RT_Kreda_15561	0
15.09.2006 03:10:00	Win-RT_Ebila_15561	100
15.09.2006 04:10:00	Win-RT_Kreda_15561	100
15.09.2006 04:40:00	Win-RT_Kreda_15561	0
15.09.2006 05:20:00	Win-RT_Ebila_15561	100
15.09.2006 05:33:00	Win-RT_Kreda_13725	0
15.09.2006 06:34:00	Win-RT_Kreda_15561	0
15.09.2006 05:20:00	Win-RT_Ebila_15561	100
15.09.2006 05:33:00	Win-RT_Kreda_13725	0
15.09.2006 06:34:00	Win-RT_Kreda_15561	0

your select:

Code:

SELECT *
FROM tab1 
 INNER JOIN (
   SELECT [NAME],count(VALUE) as count1  
   FROM tab1 
   GROUP BY [NAME] 
   HAVING count(VALUE)>2 ) 
 AS DuplicateTab1 ON Tab1.[Name]=DuplicateTab1.[Name];

the return of the query:

Code:

time	tab1.NAME	Value	DuplicateTab1.NAME	count1
15.09.2006 03:10:00	Win-RT_Ebila_15561	100	Win-RT_Ebila_15561	5
15.09.2006 05:20:00	Win-RT_Ebila_15561	100	Win-RT_Ebila_15561	5
15.09.2006 01:20:00	Win-RT_Ebila_15561	0	Win-RT_Ebila_15561	5
15.09.2006 05:20:00	Win-RT_Ebila_15561	100	Win-RT_Ebila_15561	5
15.09.2006 02:10:00	Win-RT_Ebila_15561	100	Win-RT_Ebila_15561	5
15.09.2006 01:11:00	Win-RT_Kreda_13725	100	Win-RT_Kreda_13725	5
15.09.2006 01:45:00	Win-RT_Kreda_13725	100	Win-RT_Kreda_13725	5
15.09.2006 02:50:00	Win-RT_Kreda_13725	0	Win-RT_Kreda_13725	5
15.09.2006 05:33:00	Win-RT_Kreda_13725	0	Win-RT_Kreda_13725	5
15.09.2006 05:33:00	Win-RT_Kreda_13725	0	Win-RT_Kreda_13725	5
15.09.2006 06:34:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 04:10:00	Win-RT_Kreda_15561	100	Win-RT_Kreda_15561	8
15.09.2006 01:10:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 02:12:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 04:40:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 01:40:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 06:34:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8
15.09.2006 02:55:00	Win-RT_Kreda_15561	0	Win-RT_Kreda_15561	8

one of the problem i see is that you count ALL rows with the Value "0" from one Name, but I need only consecutive rows with 3 or more "0".. not the 1 or 2 rows with a "0" between "100"...

vbkris · Sep 15, 2006

SELECT *
FROM tab1
INNER JOIN (
SELECT [NAME],count(VALUE) as count1
FROM tab1
where VALUE='0'
GROUP BY [NAME]
HAVING count(VALUE)>3 )
AS DuplicateTab1 ON Tab1.[Name]=DuplicateTab1.[Name];

I guess you were looking only for 0 entries. I gave a generic solution

...

Known is handfull, Unknown is worldfull

Rondrian · Sep 15, 2006

we are on the way...

the code:

Code:

SELECT tab1.time,tab1.Name, tab1.value
FROM tab1 
 INNER JOIN (
  SELECT [NAME],count(VALUE) as count1 
  FROM tab1 
  WHERE tab1.VALUE=0  
  GROUP BY [NAME] 
  HAVING count(VALUE)>2 ) 
 AS DuplicateTab1 ON Tab1.[Name]=DuplicateTab1.[Name]
 WHERE value=0
 ORDER BY tab1.time

the result:

Code:

time	Name	value
15.09.2006 01:10:00	Win-RT_Kreda_15561	0
15.09.2006 01:40:00	Win-RT_Kreda_15561	0
15.09.2006 02:12:00	Win-RT_Kreda_15561	0
15.09.2006 02:50:00	Win-RT_Kreda_13725	0
15.09.2006 02:55:00	Win-RT_Kreda_15561	0
15.09.2006 04:40:00	Win-RT_Kreda_15561	0
15.09.2006 05:33:00	Win-RT_Kreda_13725	0
15.09.2006 06:34:00	Win-RT_Kreda_15561	0
15.09.2006 07:33:00	Win-RT_Kreda_13725	0
15.09.2006 07:34:00	Win-RT_Kreda_15561	0

ok, now it's filtered correct the Name and the value "0" - great, but still there are to many lines because the contains also rows that doesn't have following "0" in the next iteration.
(does i understand my own sentence?!? *lol*)

example:

Code:

time                    Name                 Value
15.09.2006 01:10:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 1[/red]
15.09.2006 01:11:00    [blue]Win-RT_Kreda_13725[/blue]    100 -> [blue]"100" -> trash [/blue]
15.09.2006 01:20:00    [green]Win-RT_Ebila_15561[/green]    0   -> [green]"0" count 1[/green]
15.09.2006 01:40:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 2[/red]
15.09.2006 01:45:00    [blue]Win-RT_Kreda_13725[/blue]    100 -> [blue]"100" -> trash [/blue]
15.09.2006 02:10:00    [green]Win-RT_Ebila_15561[/green]    100 -> [green]"100" -> trash -> reset counter[/green]
15.09.2006 02:12:00    [red]Win-RT_Kreda_15561[/red]    100 -> [red]"100" -> trash -> reset counter[/red]
15.09.2006 02:50:00    [blue]Win-RT_Kreda_13725[/blue]    0    -> [blue]"0" -> count 1[/blue]
15.09.2006 02:55:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 1[/red]
15.09.2006 03:10:00    [green]Win-RT_Ebila_15561[/green]    100 -> [green]"100" -> trash[/green]
15.09.2006 04:10:00    [red]Win-RT_Kreda_15561[/red]    100 -> [red]"100" -> trash -> reset counter[/red]
15.09.2006 04:40:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 1[/red]
15.09.2006 05:20:00    [green]Win-RT_Ebila_15561[/green]    100 -> [green]"100" -> trash[/green]
15.09.2006 05:33:00    [blue]Win-RT_Kreda_13725[/blue]    0     -> [blue]"0" -> count 2[/blue]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 2[/red]
15.09.2006 05:20:00    [green]Win-RT_Ebila_15561[/green]    100 -> [green]"100" -> trash[/green]
15.09.2006 05:33:00    [blue]Win-RT_Kreda_13725[/blue]    0    -> [blue]"0" count 3 -> NOW select count 1,2 (above) and 3 from here[/blue]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" count 3 -> NOW select count 1,2 (above) and 3 from here[/red]

vbkris · Sep 15, 2006

hi,

can you repeat the same with just one user?

as far as i can see:

AS DuplicateTab1 ON Tab1.[Name]=DuplicateTab1.[Name]
WHERE value=0
ORDER BY tab1.time

will eliminate rows that dont have 0 as their values...

Known is handfull, Unknown is worldfull

Rondrian · Sep 15, 2006

uhh, i think i coulnd't explain it excatly, it's similar to these post:

http://www.tek-tips.com/viewthread.cfm?qid=934648

(but i don't understand these complex sp)

example with only one name:

Code:

time                    Name                    Value
15.09.2006 01:10:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=1)[/red]
15.09.2006 01:40:00    [red]Win-RT_Kreda_15561[/red]    0    -> [red]"0" (red)counter +1 (=2)[/red]
15.09.2006 02:12:00    [red]Win-RT_Kreda_15561[/red]    100 -> [red]"100" -> trash -> reset (red)counter (=0)[/red]
15.09.2006 02:55:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=1)[/red]
15.09.2006 04:10:00    [red]Win-RT_Kreda_15561[/red]    100 -> [red]"100" -> trash -> reset (red)counter (=0)[/red]
15.09.2006 04:40:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=1)[/red]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=2)[/red]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=3) -> NOW select count 1,2 (above) and 3 from here[/red]



so the following lines (and only these) should be shown:

15.09.2006 04:40:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=1)[/red]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=2)[/red]
15.09.2006 06:34:00    [red]Win-RT_Kreda_15561[/red]    0   -> [red]"0" (red)counter +1 (=3) -> NOW select count 1,2 (above) and 3 from here[/red]

for me the sproc have to compare the value of the current cursor position with both rows (previous cursors) before but only with these rows where the Name is the same. if on of these both contains a "100" than send the actual row to the trash, but if 2 or more rows before contains the value "0" than it should show the actual row AND the x rows before.

vbkris · Sep 15, 2006

hey, give me some time, i will simulate the same and get back to you...

Known is handfull, Unknown is worldfull

Rondrian · Sep 15, 2006

great, big thx to you vbkris

gmmastros · Sep 15, 2006

Rondrian,

Based on your data posted at 15 Sep 06 9:10, what is the expected results?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

gmmastros · Sep 15, 2006

I'm not sure I completely understand your requirements, which is why I asked the followup question. I did a little playing around and came up with this. In the code I show, there is a table variable called @Data. This exists so that I can test the code. You should be able to copy/paste all the code in to a query analyzer window and run it. If the results seem accurate, then I recommend you remove the part where I create the @Data table and populate it with data. Then, change the remainder of the code to use your table instead of the table variable.

Good Luck.

Code:

[green]-- Create the test table[/green]
Declare @Data Table(Time DateTime, Name VarChar(50), Value Int)

Set DateFormat DMY
Insert Into @Data Values('15.09.2006 01:10:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 01:11:00','Win-RT_Kreda_13725',100)
Insert Into @Data Values('15.09.2006 01:20:00','Win-RT_Ebila_15561',0)
Insert Into @Data Values('15.09.2006 01:40:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 01:45:00','Win-RT_Kreda_13725',100)
Insert Into @Data Values('15.09.2006 02:10:00','Win-RT_Ebila_15561',100)
Insert Into @Data Values('15.09.2006 02:12:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 02:50:00','Win-RT_Kreda_13725',0)
Insert Into @Data Values('15.09.2006 02:55:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 03:10:00','Win-RT_Ebila_15561',100)
Insert Into @Data Values('15.09.2006 04:10:00','Win-RT_Kreda_15561',100)
Insert Into @Data Values('15.09.2006 04:40:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 05:20:00','Win-RT_Ebila_15561',100)
Insert Into @Data Values('15.09.2006 05:33:00','Win-RT_Kreda_13725',0)
Insert Into @Data Values('15.09.2006 06:34:00','Win-RT_Kreda_15561',0)
Insert Into @Data Values('15.09.2006 05:20:00','Win-RT_Ebila_15561',100)
Insert Into @Data Values('15.09.2006 05:33:00','Win-RT_Kreda_13725',0)
Insert Into @Data Values('15.09.2006 06:34:00','Win-RT_Kreda_15561',0)

[green]-- Query code starts here[/green]

Declare @Temp Table(RowId Integer Identity(1,1), Time DateTime, Name VarChar(50), Value Int)

Insert Into @Temp(Time, Name, Value)
Select Time, Name, Value
From   @Data
Order By Name, Time
Select * From @Temp Order By Name, Time

Select Distinct 
       Time,
       Name,
       Value
From   @Temp T
       Inner Join (
         Select A.RowId As ARowId,
                B.RowId As BRowId,
                C.RowId As CRowId
         From   @Temp A
                Inner Join @Temp B 
          	  On  A.RowId = B.RowId -1 
                  And A.Name = B.Name
          	Inner Join @Temp C
                  On  B.RowId = C.RowId -1 
                  And B.Name = c.Name
         Where  A.Value = 0
                And B.Value = 0
                And C.Value = 0
       ) As A
          On T.RowId = A.ARowId
          Or T.RowId = A.BRowId
          Or T.RowId = A.CRowId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Rondrian · Sep 15, 2006

hi george,
thanks for your afford i will try it out.. to your question:

Code:

time                    Name                    Value
15.09.2006 01:10:00    Win-RT_Kreda_15561    0
[s]15.09.2006 01:11:00    Win-RT_Kreda_13725    100[/s]
[s]15.09.2006 01:20:00    Win-RT_Ebila_15561    0[/s]
15.09.2006 01:40:00    Win-RT_Kreda_15561    0
[s]15.09.2006 01:45:00    Win-RT_Kreda_13725    100[/s]
[s]15.09.2006 02:10:00    Win-RT_Ebila_15561    100[/s]
15.09.2006 02:12:00    Win-RT_Kreda_15561    0
15.09.2006 02:50:00    Win-RT_Kreda_13725    0
15.09.2006 02:55:00    Win-RT_Kreda_15561    0
[s]15.09.2006 03:10:00    Win-RT_Ebila_15561    100[/s]
[s]15.09.2006 04:10:00    Win-RT_Kreda_15561    100[/s]
15.09.2006 04:40:00    Win-RT_Kreda_15561    0
[s]15.09.2006 05:20:00    Win-RT_Ebila_15561    100[/s]
15.09.2006 05:33:00    Win-RT_Kreda_13725    0
15.09.2006 06:34:00    Win-RT_Kreda_15561    0
[s]15.09.2006 05:20:00    Win-RT_Ebila_15561    100[/s]
15.09.2006 05:33:00    Win-RT_Kreda_13725    0
15.09.2006 06:34:00    Win-RT_Kreda_15561    0

Rondrian · Sep 15, 2006

hey, your query seems to be nice, but the last 2 rows (05:33 and 06:34) are not in the result, but they have to... (the last both lines)

gmmastros · Sep 15, 2006

The last 2 rows in the sample data are complete duplicates of rows that appear before it.

Code:

[blue]15.09.2006 05:33:00    Win-RT_Kreda_13725    0[/blue]
[purple]15.09.2006 06:34:00    Win-RT_Kreda_15561    0[/purple]
[s]15.09.2006 05:20:00    Win-RT_Ebila_15561    100[/s]
[blue]15.09.2006 05:33:00    Win-RT_Kreda_13725    0[/blue]
[purple]15.09.2006 06:34:00    Win-RT_Kreda_15561    0[/purple]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Rondrian · Sep 15, 2006

oh fu... you're right, the times should be different, my mistake, but i've tested it with correct times and... it works!! :-D

cool, big thanks for your code! now the only thing for me to do is... how could i understand your code!?! ;-)

gmmastros · Sep 15, 2006

There's a couple interesting techniques used to get the output data the way you want. I will attempt to exmplain.

First, you need to find those records that have 3 consecutive 0's in the value field (per name). My initial thoughts were to use a self join (where you join the table bak to itself). Based on your data, this was problematic because there was no field that could be used for this self join. To do the self join we need a way to know the order of the records returned such that all records for a particular user is grouped together and ordered by time. This is where the @Temp table variable comes in. Notice this part...

Code:

Declare @Temp 
Table   (RowId Integer Identity(1,1), 
        Time DateTime, 
        Name VarChar(50), 
        Value Int)

Insert Into @Temp(Time, Name, Value)
Select Time, Name, Value
From   [!]TableName[/!]
Order By Name, Time

First, we create a table variable with a RowId column set to integer identity(1,1). With the Identity field, each record added to the table will have a unique integer value, starting at 1 and incrementing by 1. Next, we add the records to the table variable, sorted first by name then by time (so the records for each user will be grouped together and the sorted by time).

Now, we have a RowId column that we can use to perform the self join. We join the tables where the names are the same and the rowid from 1 record is the same as the rowid - 1 of the next record. This join is done twice (so the table is used 3 times) based on your requirements of having 3 consecutive 0's.

This part...

Code:

Select A.RowId As ARowId,
       B.RowId As BRowId,
       C.RowId As CRowId
From   @Temp A
       Inner Join @Temp B 
         On  A.RowId = B.RowId -1 
         And A.Name = B.Name
       Inner Join @Temp C
         On  B.RowId = C.RowId -1 
         And B.Name = c.Name
Where	A.Value = 0
        And B.Value = 0
        And C.Value = 0

The Join on RowId's causes consecutive records to be returned. The where clause causes only those records where the value is 0 to be returned. Net effect, 3 consective 0's.

Then, we make this be a subquery and join back to the original data based on rowid's.

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Rondrian · Sep 15, 2006

hi george,
YES IT MAKE SENSE NOW! (and i accomplished to modify your code for my real data)

in germany we would say "HUT AB!" ("HATS OFF!") for that cool code! ok, it is really slow with many rows (i've tried with ~3000 rows -> ~15min on a Quad Xeon with 8192 gig RAM), but... who cares

so i do now a SQL Job at night which will correlate one day in an table and for my frontend i use than directly that table, that's ok for me!

i believe i could never code SQL like you so big thanks for your troubles with me

greetings from germany
andy

gmmastros · Sep 15, 2006

Andy,

I'm glad that I could help.

Regarding the performance issue...

Most of the time in the procedure will be spent dealing with the RowId's, so indexing that may speed things up considerably. In the code where you create the table variable, try specifying the primary key, like this...

Code:

Declare @Temp 
Table   (RowId Integer Identity(1,1) [!]Primary Key Clustered[/!], 
        Time DateTime, 
        Name VarChar(50), 
        Value Int)

If this doesn't make a noticeable improvement, you could also try...

Code:

Declare @Temp 
Table   (RowId Integer Identity(1,1), 
        Time DateTime, 
        Name VarChar(50), 
        Value Int
        [!]Primary Key Clustered (RowId, Name)[/!]
        )

3000 rows isn't really that much, so indexes should improve performance considerably. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Stored Procedure which skips rows in Select (Cursor?) 1

ISP

Programmer

Programmer

ISP

Programmer

ISP

Programmer

ISP

Programmer

ISP

Programmer

Programmer

ISP

ISP

Programmer

ISP

Programmer

ISP

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor