February 9, 2011 at 1:32 pm
I have the table A
where I have cols like,
Region, Doctors name, sumofvissits, 100percentrecon, PercentRecon ---( I have put 0 in the select staement in it's place)
Now I want to update the tableA
Update TableA
Set PercentRecon = (100percentrecon/sumofvissits) *100
once I run the update statement it runs but when I look at the table it did not update the col Percentrecon, it sill keeps 0. In the table A,100percentrecon =2 and sumofvissits 27
so the percentage should be 7.07%???
Please help.
Thanks,
Hai
February 9, 2011 at 1:45 pm
what is the table definition? instead of running the update statement, select (100percentrecon/sumofvissits) *100 from the table to see what you get. If the 2 fields are integers, then you won't get any decimals. you'll have to cast the values to get decimals. Here's an easy example:
select 10/3, CAST(10 as decimal(8,4))/CAST(3 as decimal(8,4))
You don't have to cast both values, but I did it for consistency
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 1:46 pm
Did you commit?
Post definition of the table,
Post session showing update statement.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 9, 2011 at 1:57 pm
To elaborate on Mike01 - example
Run these 2 snippets of T-SQL and notice what happens
DECLARE @N DECIMAL(5,2)
DECLARE @sumofvissits INT
DECLARE @Xpercentrecon INT
SET @N = 0.
SET @sumofvissits =27
SET @Xpercentrecon =2
SET @N = @xpercentrecon/@sumofvissits
SELECT @N
--The above will return 0.00
DECLARE @N DECIMAL(5,2)
DECLARE @sumofvissits DECIMAL(5,2)
DECLARE @Xpercentrecon DECIMAL(5,2)
SET @N = 0.
SET @sumofvissits =27
SET @Xpercentrecon =2
SET @N = @xpercentrecon/@sumofvissits
SELECT @N
--This returns 0.07
February 9, 2011 at 2:27 pm
Thank you very much. It WORKED!!!
February 10, 2011 at 9:05 am
Sorry, it is not working.
Update #Final_Table ---select * from #Final_Table
SET PercentRecon = (CAST (tot_100pervisitscnt as decimal(8,4))/CAST(sumofvisitCounts as decimal (8,4)))
where sumofvisitCounts>0
giving me the error message
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
where I had 00.000 as PercentRecon in the select Statement.
Please help,
Hai
February 10, 2011 at 9:09 am
The cast is only allowing numbers less than 10000, so increase the cast to 9,4 or 10,4, etc..
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2011 at 10:07 am
I am not getting the right results,
even though I modified...the CAST .
I have 0 as PercentRecon (place holder for updating this field) and then
Update #Final_Table ---select * from #Final_Table
SET PercentRecon = (CAST (tot_100pervisitscnt as decimal(10,4))/CAST(sumofvisitCounts as decimal (10,4))) *100
where sumofvisitCounts>0
In PercentRecon col, I am getting whole numbers like 7, 11...
I am not getting in decimal format.
Please help,
Thanks,
Hai
February 10, 2011 at 10:16 am
I am thinking your issue is that the PercentRecon column is an integer???
When you divide by sumofvisits you will get a value less than 1, unles they are equal (which for an integer will always be 0).
to fix it do this:
Set PercentRecon = (100percentrecon * 100)/sumofvissits
instead of:
Set PercentRecon = (100percentrecon/sumofvissits) *100
The probability of survival is inversely proportional to the angle of arrival.
February 10, 2011 at 10:18 am
can you post the table script and some sample data?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2011 at 11:02 am
Select [Quarter Name], Region, [Provider Name],
SUM([Total Visisits Count]) as SumofVisitCounts, sum([100% reconciled]) as [100% Reconciled],
0 as tot_100pervisitscnt, 0 as PercentRecon
into #Final_table
from #SemiFinal_Table
group by [Quarter Name], Region, [Provider Name]
order by [Quarter Name], Region, [Provider Name]
Update #Final_Table ---select * from #Final_Table
SET PercentRecon = (CAST (tot_100pervisitscnt as decimal(9,4))*100/CAST(sumofvisitCounts as decimal (9,4)))
modifying the update code from above did not work though
Update #Final_Table ---select * from #Final_Table
SET PercentRecon = (CAST (tot_100pervisitscnt as decimal(9,4))*100/CAST(sumofvisitCounts as decimal (9,4)))
Please help.
Thanks,
Hai
where sumofvisitCounts>0
Provider NameSumofVisitCounts100% Reconciled tot_100pervisitscnt Percent
Recon
Dr Smith27227
4000
Dr MAria617711
February 10, 2011 at 12:05 pm
Hai
Select [Quarter Name], Region, [Provider Name],
SUM([Total Visisits Count]) as SumofVisitCounts, sum([100% reconciled]) as [100% Reconciled],
0 as tot_100pervisitscnt, 0 as PercentRecon
into #Final_table
from #SemiFinal_Table
cast the PercentRecon and tot_100pervisitcnt to 0 will make them int. try cast(0 as decimal(9,4)) for each or create the #Final_Table before inserting into it and declare the fields as decimal there
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2011 at 1:53 pm
Thanks a lot. It worked!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply