Script to divide data by 2
-
Greetings,
I need a script to divide the contents of two columns by the number 2. Each column contains payroll information and because of an error with the accounting program the information for each person is twice the amount it should be. There are about 350 rows that contain the incorrect data. The identical data is contained in two separate columns so the script has to be able to select both columns and divide the information in each row by 2 and replace the current information.
Can anyone help me with this?
Thanks
-
First do this to make sure you get what you're looking for before updating the table:
select ((col1 + col2)/2) as rightInfo
from table1
where info = wrongInfo
you say that you have 2 columns with identical data - when used correctly what is the second column used for ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Please post the table definition and some sample data and most importantly the expected output after the update... It's not something we can screw up on
. -
Thanks I'll give this a try. Why does the table have two columns with the same info? Beats me...the columns have different headings but I'm not an accounting guru so I don't know what it all means. I just have to make it work!
-
Dave - what I meant was - in the 350 rows that are incorrect, they both have the same values...but what about all the other (remaining) "right" rows ?! What info does the second column store in those ?!
**ASCII stupid question, get a stupid ANSI !!!** -
The rows that are incorrect contained direct deposit info, the other rows are correct because they are check info.
Your solution
select ((col1 + col2)/2) as rightInfo
from table1
where info = wrongInfo
worked so thanks for your help.
-
Thanks for the feedback - I've just finished reading the contents of another forum where people were complaining about lack of feedback...:
**ASCII stupid question, get a stupid ANSI !!!** -
I think it means you have a bad DB design.
But not a problem just start writing code while someone finds out what they want.
-
what results do you get when you
select ((col1 + col2)/2) as rightInfo
from table1
where info = wrongInfo
(BTW this does not change any values)
If col1 and col2 contain the same info then the result should be the value of col1 if both columns are equal. It does not tell you if the value in col1 is correct.
-
Shouldn't the data represent the correct value?? How can yo know the correct value if you don't know what the data represents?? I think you're going the wrong way to solve this problem.
-
Mike - I completely overlooked the fact that the 2 columns have duplicate data (stupid me..) - he should actually be updating col2 with the right data...
I think (therefore I am...;-) - that when the 2 values are the same then they are the "wrong rows" and otherwise they are correct...(or something like that...) but we won't know till Dave gets back....
**ASCII stupid question, get a stupid ANSI !!!** -
I was refering to the fact that Dave said that the payroll information was double. Does this mean that the data in both columns contain incorrect information (col1 is 2 X actual information) or that an application is adding the values and reporting the sum which would be 2 X the correct information if both columns contain valid data. You were right the first time to solve this we need the DDL, sample data and the expected results.
-
If both columns contain the same data how will updating col2 help?
I think we started the race before the starting gun was fired. I at least need more information as my crystal ball is not working this morningMike
-
Maybe I wasn't clear with this message
. -
That's the second time you've misunderstood something I've said in just one morning 🙁
I meant update the 2nd column with right data (whatever that is)...also if the values in both columns are same then we don't need to divide by 2...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply