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.
| Author |
Topic |
|
NguyenL71
Posting Yak Master
228 Posts |
 Posted - 2011-02-07 : 12:03:44
|
| [code]Hello,How can I round 10 1% if the values .0001 and .9999 then round to 99 or 99.9%. Given the simple business rule below.I am using SQL 2008. Thank you very much in advance.DROP TABLE [dbo].[#Temp]GOCREATE TABLE [dbo].[#Temp]( [OrderId] [int] NOT NULL, [ActionRequired] [varchar](10) NOT NULL, [Completed] [int] NULL, [Total] [int] NULL, [pctCompleted] [decimal](10, 2) NULL) ON [PRIMARY]GOBEGIN TRANSACTION;INSERT INTO [dbo].[#Temp]([OrderId], [ActionRequired], [Completed], [Total], [pctCompleted])SELECT 9, N'Review', 1, 1000, 0.00UNION ALLSELECT 9, N'Review', 9999, 1000, 0.00COMMIT;RAISERROR (N'[dbo].[TestTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;GO--Testing... SELECT (1. * Completed)/Total FROM #Temp; go ---------------0.001000000009.99900000000 -- Business rule: 1. If < .001 then round to 1%/ 2. if .999 then round to 99% or 99.9% either case. Prefer 99%-- Result want:OrderId ActionRequired Completed Total pctCompleted----------- -------------- ----------- ----------- -------------9 Review 1 1000 1%9 Review 9999 1000 99%[/code] |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-07 : 13:24:21
|
| See my perception regarding your business rules 1) first of all the first row is not less than .001 ..i think its Equal to .001. Secondly and mathematically it is not equal to 1% rather its equal to .1% as if it was 1 % then the figure under completed should have been equal to 10.2) same mathematical rule apply for the second row however in your case it seems to be a replacement of fixed values .. which could be achieved through transact SQL CASE |
 |
|
|
|
|
|
|
|
Help with Percentage round.