August 19, 2010 at 10:38 pm
Hi to everyone, Is there a function in T-SQL 2000 that can solve this problem?
Here is the sample data.
CREATE TABLE #tempd (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
insert into #tempd(Amount)values(13230)
insert into #tempd(Amount)values(12400)
insert into #tempd(Amount)values(11615)
insert into #tempd(Amount)values(9245)
Here is the sample output.
CREATE TABLE #output (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
insert into #output(Amount)values(12400)
insert into #output(Amount)values(13230)
insert into #output(Amount)values(9245)
insert into #output(Amount)values(11615)
what happen is that it updates the row randomly using there own value. like for example the original value of ID:1 is Amount=13230 but after random updates it become 12400.
Is there a function for me to solve this problem?
Thanks in advance
August 20, 2010 at 1:34 am
CAST(
(RAND() * 10000 + RAND() * 10000 + RAND() * 10000 + RAND() * 10000 + RAND() * 10000) +
(RAND() * 1000 + RAND() * 1000 + RAND() * 1000 + RAND() * 1000 + RAND() * 1000) +
(RAND() * 100 + RAND() * 100 + RAND() * 100 + RAND() * 100 + RAND() * 100) +
(RAND() * 10 + RAND() * 10 + RAND() * 10 + RAND() * 10 + RAND() * 10)
AS Int)
Use rand() function to get random value, then update your column with random value
August 20, 2010 at 1:48 am
InfiniteError (8/19/2010)
what happen is that it updates the row randomly using there own value. like for example the original value of ID:1 is Amount=13230 but after random updates it become 12400.
It looks as if what you're trying to do is change the order of the values in the Amount column, so that you always have the same values in your #tempd table and your #output table, just in a different order. Is that right? Why do you need to do this, incidentally?
John
August 20, 2010 at 2:12 am
Thanks for the response,
well honestly it all start with a joke in one of my manager, to exchange all the amount although i don't have any authority to our Production DB, I just want to try to achieve it and take it as a challenge.
August 24, 2010 at 7:07 am
INSERT INTO #output(Amount) SELECT Amount FROM #tempd ORDER BY NEWID() ASC
Far away is close at hand in the images of elsewhere.
Anon.
August 24, 2010 at 7:17 am
David Burrows (8/24/2010)
INSERT INTO #output(Amount) SELECT Amount FROM #tempd ORDER BY NEWID() ASC
David
I thought of that, and it would certainly work for the sample data provided. However, I reasoned that if there were gaps in the identity sequence and the original poster wanted to keep the same set of values in the ID column, we'd need something more sophisticated. That's where I got stuck. It would be very easy on SQL Server 2005 and above!
John
August 24, 2010 at 10:08 am
John Mitchell-245523 (8/24/2010)
David Burrows (8/24/2010)
INSERT INTO #output(Amount) SELECT Amount FROM #tempd ORDER BY NEWID() ASCDavid
I thought of that, and it would certainly work for the sample data provided. However, I reasoned that if there were gaps in the identity sequence and the original poster wanted to keep the same set of values in the ID column, we'd need something more sophisticated. That's where I got stuck. It would be very easy on SQL Server 2005 and above!
John
Good catch John 🙂
In that case it could be done with two temp tables, one to allocate new sequential ID to the data and the second like above to randomise the amount
Far away is close at hand in the images of elsewhere.
Anon.
August 26, 2010 at 11:18 pm
the original poster wanted to keep the same set of values in the ID column
IDENTITY property on ID column suggests otherwise.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply