Date parsed: 19/10/2007 08:42:14
Date: Fri, 19 Oct 2007 07:42:14 +0100

"aquagal" <bhavanab@gmail.com> wrote in message
news:1192754572.481781.79430@v29g2000prd.googlegroups.com...
> Hi all,
>
> 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 am 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 table 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
>

I don't think you'll need CLR for this. Here's my first attempt, which
should hopefully be close to what you want:

-- Delete to depth
DELETE FROM tbl
WHERE RowID <
(SELECT MIN(RowID)
FROM
(SELECT MIN(RowID)
FROM tbl
WHERE ColA >0
UNION
SELECT MIN(ColB)
FROM tbl
WHERE ColB >0) d(RowID));

-- Update invalid column values
UPDATE tbl
SET ColA =
CASE WHEN ColA < 0 THEN
(SELECT TOP 1
ColA
FROM tbl t
WHERE RowID < tbl.RowID
ORDER BY RowID DESC)
ELSE ColA END,
ColB =
CASE WHEN ColB < 0 THEN
(SELECT TOP 1
ColB
FROM tbl t
WHERE RowID < tbl.RowID
ORDER BY RowID DESC)
ELSE ColB END;

You should understand that even when using CLR code, you still need to call
T-SQL in order to update data in tables. CLR isn't usually good for
data-manipulation operations.

--
David Portas