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
>

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--