change certain values in sql - 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

Transact-SQL (2000)

change certain values in sql

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-18 : 18:13:13

When I run a particular SP I get the following result:

2005-03-25 0.12866403372827406
2005-03-28 0.12866403372827406
2005-03-29 0.12866403372827406
2005-03-30 0.21755070072822402 *******
2005-03-31 7.8753875682858299E-2
2005-04-01 7.8753875682858299E-2
2005-04-04 7.8753875682858299E-2

If you notice i get one value where it is unique ie-.2175507......
Is there any sql I can use to change this value of .21...into the previous # of .128664...? The dates will always change, so I won't be able to use a condition on the dates......maybe something with a count function??

Any help would be appreciated!
Dave

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-18 : 18:53:15

What is the primary key of this table?

Nathan Skerl

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-18 : 18:58:14

There is no primary key. The only unique values for every record would be the date.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-18 : 19:00:48

How do you want to deal with tihs situation:

2005-03-25 0.12866403372827406
2005-03-27 0.12866403372827406
2005-03-28 0.12866403372827406
2005-03-29 0.99999999999999999 *******
2005-03-30 0.21755070072822402 *******
2005-03-31 7.8753875682858299E-2
2005-04-01 7.8753875682858299E-2
2005-04-04 7.8753875682858299E-2

Nathan Skerl

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-18 : 19:04:12

I would want to change both .999999 and .2175507...to .128664033.....

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-18 : 19:48:19

Here is my first crack... I have to run, but im sure we can come up with something a bit more elegant tomorrow

Does this do what we want?

set nocount on

declare @table table (dt datetime, value varchar(50))
insert into @table (dt, value)
select '2005-03-25', '0.12866403372827406' union all
select '2005-03-28', '0.12866403372827406' union all
select '2005-03-29', '0.12866403372827406' union all
select '2005-03-30', '0.99999999999999999' union all
select '2005-03-31', '0.21755070072822402 *******' union all
select '2005-04-01', '0.12000000000000000' union all
select '2005-04-01', '0.12000000000000000' union all
select '2005-04-02', '0.12000000000000111'


select * from @table 
order by dt

------------------------------
-- do the update
------------------------------
update t
set value = dd.value
from @table t
inner join (	select 	max(dt) mdt, 
			value, 
			count(*) cnt
		from @table
		group by value
		having count(*) = 1)d
on t.dt = d.mdt
left join (	select 	max(dt) mdt, 
			value, 
			count(*) cnt
		from @table
		group by value
		having count(*) > 1)dd
on t.dt > dd.mdt


select * from @table 
order by dt

Nathan Skerl

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-19 : 08:56:00

I think that should do it. Thanks for the help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-19 : 11:13:42

I didnt take into account what to do when you have a distinct value with no previous non-distinct value to update from:

ex:

select '2005-03-24', '0.XX' union all
select '2005-03-25', '0.12866403372827406' union all
select '2005-03-28', '0.12866403372827406' union all
...

if you want to leave it as is:

set value = isnull(dd.value, t.value)

or come up with some business logic around that.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources