further to problem with obtaining first/last occurance of contiguous blocks of data

Hi again, I was hoping someone could help me create a sql query to
minimizing the content in my table based on a few rules.
Further to my previous post...
f2b9da63" target="_blank">http://groups.google.com.au/group/m...br />
f2b9da63
... I have decided to use triggers to process my table (see trigger solutio
n
in my previous post). However since triggers are slow at processing, I aim
to search for a solution to minimize my initial table structure so the
triggers have less data to work with (hence process it much quicker). What I
am looking for is a method to perform the following:
This is my initial table (as an example):
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
I want my final table to look like this:
[system] [date] [isOn]
A 01 0
A 04 1
-
A 06 0
A 20 1
A 21 0
-
A 27 1
-
-
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
-
-
-
B 67 0
... where i have placed a '-' sign to indicate the rows i need deleted
I am deleting rows under the following conditions:
1. for a contiguous block of 1's WITHIN the same client range, delete all
but the first one ( i.e.the one at the earliest date)
2. for a contiguous block of 0's WITHIN the same client range, delete all
but the first one (i.e. the one at the earliest date)
i.e. notice above that even though there is a contiguous block of 1's from
date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
B.
After this table is thus processed, I can use my cursor on it.
Any help would be really appreciated!
many thanks
wileyI have scrapped my cursor implementation for a much faster set-based
approach put forward by Itzik Ben-Gan (as im my previous post). Thanks
everyone!
cheers
wiley
"wiley" <wiley@.nospam.com> wrote in message
news:uK5SI4SFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi again, I was hoping someone could help me create a sql query to
> minimizing the content in my table based on a few rules.
> Further to my previous post...
> 29f2b9da63" target="_blank">http://groups.google.com.au/group/m... />
29f2b9da63
> ... I have decided to use triggers to process my table (see trigger
> solution in my previous post). However since triggers are slow at
> processing, I aim to search for a solution to minimize my initial table
> structure so the triggers have less data to work with (hence process it
> much quicker). What I am looking for is a method to perform the following:
> This is my initial table (as an example):
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
> I want my final table to look like this:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> -
> A 06 0
> A 20 1
> A 21 0
> -
> A 27 1
> -
> -
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> -
> -
> -
> B 67 0
> ... where i have placed a '-' sign to indicate the rows i need deleted
> I am deleting rows under the following conditions:
> 1. for a contiguous block of 1's WITHIN the same client range, delete all
> but the first one ( i.e.the one at the earliest date)
> 2. for a contiguous block of 0's WITHIN the same client range, delete all
> but the first one (i.e. the one at the earliest date)
> i.e. notice above that even though there is a contiguous block of 1's from
> date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
> B.
> After this table is thus processed, I can use my cursor on it.
> Any help would be really appreciated!
> many thanks
> wiley
>