Curs'ed - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Curs'ed

Author

Topic

kingroon
Starting Member

29 Posts

Posted - 2007-01-22 : 00:35:54

Hi..

I have been given the task of writing a DTS script to check the integrity of numeric data in a table. This task will run every 12 hours.

I have to test the value of one row with the average of the previous 20 and next 20 rows and produce a %age difference. If this difference is greater than 5%, the row is to be marked for deletion [add ID to a temp table I was thinking].

Can this be done without a Cursor? Given that in any given 12 hour period, there are going to be around 2000 new records..

Any advice or methodologies gratefully received

Rock on..
KingRoon

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 00:49:15

Yes, it can be done without a CURSOR.
We don't like guessing, so if you want some useful answers, please provide some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

kingroon
Starting Member

29 Posts

Posted - 2007-01-22 : 17:43:33

Hi Peso,

Here is some example data..

columnID columnValue
3565026 1.2678288431061806
3565042 1.2678288431061806
3565058 1.2671862130140024
3565074 1.2675074466062488
3565090 1.2677484787018256
3565106 1.2673468094544071
3565122 1.2671862130140024
3565138 1.2677083003200964
3565154 1.2673869649250655
3565170 1.3600000000000000*
3565186 1.2676681244850097
3565203 1.2660631765525101
3565219 1.2648621300278271
3565235 1.2654223347042075
3565251 1.2655024044545684
3565267 1.2656625743576762
3565283 1.2659830358273199
3565299 1.2659830358273199
3565315 1.2659029052471678
3565331 1.2659029052471678
3565347 1.2661433274246643

For this example, let's say I am comparing row 11 [with the asterisk] to the previous ten rows and the next ten rows.

I wish to check that this row 11 has more than 5% [lesser or greater] difference than the mean of the rest of the comparison data. Note that the IDs are not sequential, but are unique IDs.

Expected output here is a boolean True, hence the ID is returned [so I can deal with as is necessary].

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-01-22 : 18:21:02

Have a quick read of this http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx and you'll find you get a better quality answer.

Say hi to Dave for me.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

kingroon
Starting Member

29 Posts

Posted - 2007-01-22 : 18:31:49

Hi Merkin, cheers for the link, I think that page has been hit by a bit of spam? Kinda stacking my browser..

Will have a read when IE7 has sorted itself out..

Dave says G'day

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 01:41:56

[code]-- prepare sample data
declare @t table (columnid int, columnvalue numeric(38, 16))

insert @t
select 3565026, 1.2678288431061806 union all
select 3565042, 1.2678288431061806 union all
select 3565058, 1.2671862130140024 union all
select 3565074, 1.2675074466062488 union all
select 3565090, 1.2677484787018256 union all
select 3565106, 1.2673468094544071 union all
select 3565122, 1.2671862130140024 union all
select 3565138, 1.2677083003200964 union all
select 3565154, 1.2673869649250655 union all
select 3565170, 1.3600000000000000 union all
select 3565186, 1.2676681244850097 union all
select 3565203, 1.2660631765525101 union all
select 3565219, 1.2648621300278271 union all
select 3565235, 1.2654223347042075 union all
select 3565251, 1.2655024044545684 union all
select 3565267, 1.2656625743576762 union all
select 3565283, 1.2659830358273199 union all
select 3565299, 1.2659830358273199 union all
select 3565315, 1.2659029052471678 union all
select 3565331, 1.2659029052471678 union all
select 3565347, 1.2661433274246643

-- stage the data
declare @s table (rowid int identity, columnid int, columnvalue numeric(38, 16), runningvalue numeric(38, 16))

insert @s
(
columnid,
columnvalue
)
select columnid,
columnvalue
from @t
order by columnid

-- update the running value, average of the previous 10 records and following 10 records, not including self
update s
set s.runningvalue = (select avg(columnvalue) from @s as x where x.rowid >= s.rowid - 10 and s x.rowid <= x s.rowid + 10 and x.rowid <> s.rowid)
from @s as s

-- show which records that exceeds +/- 5%
select *
from @s
where columnvalue > 1.05 * runningvalue
or columnvalue < runningvalue / 1.05[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 04:37:01

Depending of what you mean with +/- 5%, this can replace the WHERE above.

where	columnvalue > 1.05 * runningvalue
	or columnvalue < 0.95 * runningvalue

Peter Larsson
Helsingborg, Sweden

kingroon
Starting Member

29 Posts

Posted - 2007-01-23 : 16:55:40

Peso, Legend, cheers mate..

I won't post up the Cursor action I had going here.. Sufficed to say it was nowhere near as quick as your script..

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources