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.
| Author |
Topic |
|
coolaqua
Starting Member
1 Post |
 Posted - 2007-10-18 : 20:37:33
|
Hi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is whether it is possible and feasible to do this kind of processing using CLR integration or should I stick to T-SQL ?
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's an example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.
So delete 4 rows from the beginning of the table
So the output will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua
|
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 10:58:22
|
if you have a set number of columns you can try the below repeated for each column and changing the column name used. if the number of columns is expected to grow, or there are MANY, you can turn this into dynamic sql based on the INFORMATION_SCHEMA.COLUMNS view. good luck...
declare @sample table (rowid int, cola int, colb int)
insert @sample
select 1,0,-6
union all
select 2,-5,0
union all
select 3,-3,0
union all
select 4,1,-7
union all
select 5,4,4
union all
select 6,-9,8
union all
select 7,5,-5
union all
select 8,0,-8
--colA
update sample
set cola = validrow.cola
from @sample as sample
join
(
select invalid.rowid, MAX(previously_valid.rowid) as validrowid
from @sample as invalid
join @sample as previously_valid
on previously_valid.rowid < invalid.rowid
and previously_valid.cola > 0
where invalid.cola <= 0
group by invalid.rowid
) as correction
on sample.rowid = correction.rowid
join @sample as validrow
on validrow.rowid = correction.validrowid
delete from @sample where cola <= 0
--colB
update sample
set colb = validrow.colb
from @sample as sample
join
(
select invalid.rowid, MAX(previously_valid.rowid) as validrowid
from @sample as invalid
join @sample as previously_valid
on previously_valid.rowid < invalid.rowid
and previously_valid.colb > 0
where invalid.colb <= 0
group by invalid.rowid
) as correction
on sample.rowid = correction.rowid
join @sample as validrow
on validrow.rowid = correction.validrowid
delete from @sample where colb <= 0
|
 |
|
|
|
|
|
|
|
CLR integration and complex column processing