Rounding Values
Apr 6, 2006Hi,
How can I round a value to the next int number like all values > 1 and < 2 I need to round to 2 and on and on...to all numbers
So If I have 2.1 it's 3 if I have 2.9 it's 3 ...and so on...
Thanks
Hi,
How can I round a value to the next int number like all values > 1 and < 2 I need to round to 2 and on and on...to all numbers
So If I have 2.1 it's 3 if I have 2.9 it's 3 ...and so on...
Thanks
I am using SQL 2000 in a kind of electronic wallet way. Users out money onto an account and spend it on various services on a system. The cost of those services is deducted from the value in their wallet, and everybody's happy. However, some very strange things have been happening to my transactions; seemingly at random.
Some transactions (such as purchasing time on the Internet) are returning values such as 0.10000000000000001 instead of 0.1. This minute difference affects the user's wallet balance because the rouge digit is subtracted from their account. So instead of a balance of, say, 3.4 they have 3.39999999999999999.
"So what?", I hear you say. Well the problem comes when it's time to give them a refund. They walk over to a kiosk and the machine tells them they have 3.40 remaining in their account (it's nicely rounding up the value), but when they click Refund, it tells them they have insufficient funds to complete the refund! (Note: The refund amount is being compared with the wallet balance). If I go into the database via Query Analyzer it tells me their balance is 3.3999999etc, but in Enterprise Manager the value is 3.4. If try to manipulate the data in any way it is treated as 3.4. However, if I add 0.000000000000001 then QA reads the value as 3.4 and the customer can get their refund.
My questions is this. One, how the hell do I stop this from happening? I only need the two decimal places. Taking the value in a query and round it up/chopping off the remaining decimal points hasn't worked. It always picks up the value as 3.4 in a query. Two, why on Earth is this happening??? Has anyone experienced this problem before.
Thanks in advance to anyone that's read this far down.
Hi all
I have a Float Field in my table with following values:
1.63
7.42
35.71
0.58
every thing is ok BUT when i Select Data from this table in
QUERY ANALIZER environment with (Select * from mytable) , Query Analizer shows me the following values:
1.6299999999999999
7.4199999999999999
35.710000000000001
0.57999999999999996
Why? while i don't have this problem in Enterprise manager !!!
Thanks in advance.
Kind Regards.
I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.
SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
View 9 Replies View RelatedI am using SSRS 2014. I'm using a matrix instead of a tablix because it allows me to have dynamic columns. In the example I'm showing, two of the columns use the sum function to get the total counts per practice. The third column contains percentages so I averaged for the total but the value is inaccurate compared to the value I would get if the divided the the two totals that are sums of the counts. Is there a way for me to specify that I want to divide the total counts numerator divided by the total counts denominator?
Here's an example report output with the percentage column averaged (inaccurate):
PCP numerator denominator percentage
John Smith 66 104 63.46
Tom Jones 4 36 11.11
.
.
.
Jane Doe 1 1 100
Total 708 1005 72.3
So the 72.3 value is from Avg(metricvalue)
I would like to do this instead: % total = 708/1005, which equals 70.5 - a significant difference.
The metricvalue column is what is the value for every number above (Because it's a matrix).
I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:return (decimal)12.47 It returns the correct value, however if I set it with an expression like this:return (decimal)arParam[1].Value;It rounds the number up: How can I get it to not round up when insertign a value based ona expression?
public decimal GetCreditPrice(string CustomerSecurityKey)
{
try
{
System.Data.SqlClient.SqlParameter prmCrnt;
System.Data.SqlClient.SqlParameter[] arParam = new System.Data.SqlClient.SqlParameter[2];
prmCrnt = new System.Data.SqlClient.SqlParameter("@CustomerSecurityKey", SqlDbType.VarChar,25);
prmCrnt.Value = CustomerSecurityKey;
arParam[0] = prmCrnt;
prmCrnt = new System.Data.SqlClient.SqlParameter("@Price", SqlDbType.Decimal);
prmCrnt.Direction = ParameterDirection.Output;
arParam[1] = prmCrnt;
SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam);
return (decimal)arParam[1].Value;
}
catch(System.Exception ex)
{
throw ex;
}
}
I have the following statement and I want to round the final value(gbkmut.bdr_hfl)two decimal places.
begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end
all,
i want to round a number, for example 8.50 be 9
i use math.round
it works when it's like math.Round(8.50)
the result would be 9
but if i do this math.Round(max(Fields)) or max(math.Round(Fields))
> assume the Fields value's 8.50, the result is 8
anybody know how to solve this?
thanks!
Addin
I'm passing a value from an ASP.net app to an sql stored procedure which stores it in a table.
Problem is if, for example, the value is 2.81 the value is ending up as 2.08999... in my table, but if i do say 6.3 it's fine.
Ive tried having the variable in asp and the field where its stored in sql as a number of types but all with the same result.
Any ideas?
Geoff.
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.
Any solutions?
I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @FCost. Then I round @FCost=Round(@FCost,2).
When I try to inert this value to a new record again I'using Round(@FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
Manolis
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up
Does this make sense?
I need to add 3% to my prices
UPDATE prices SET price = price * 1.03
But I also need to round up to the nearest 99p so for example 1.13 becomes 1.99
How can I do this, I presume I need to round up to the nearest whole number, i.e. Math.Ceiling and then -0.01 but I dont know the syntax in SQL.
Thank you
I need help on this query. I'm trying to have a number rounded, so I can truncate the decimal. The reason I want to do this is that it is for a planning function and I need it to round to a number that is divisible by an order minimum qty. Example: I show a need for 2611 items, but the item is only ordered in qtys of 100, so I'd need 2600 instead of 2611, because the vendor won't let me order out of qty. So, my query would take 2611 / minimum order qty (100) which would be 26.11 somehow take off the .11 then multiply back by 100, which would give me 2600.
use mas500test_app
-- UPDATE timItem
-- SET UserFld3 = 1
Select distinct I.ItemID, V.VendID, D.ShortDesc, B.Name as ItemBuyer, BV.Name as VendorBuyer,
IC.ItemClassID, PPL.PurchProdLineID, isNUll(BI.QtyOnHand,0) AS QtyOnHand,
IV.QtyOnPO, IV.QtyONSo, IV.QtyONBo, W.WhseID, ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) as Available,
IV.MaxStockQty, IV.MinStockQty, IV.MaxStockQty - IV.MinStockQty AS SafetyStock, I.UserFld6 as Rank, I.UserFld3,
-- Case
-- WHEN I.UserFld3 = 0
-- THEN '1'
-- ELSE I.UserFld3
-- END
-- as PackQty,
CASE
WHEN (IV.MaxStockQty - IV.MinStockQty) <> 0
THEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / (IV.MaxStockQty - IV.MinStockQty))
ELSE 0
END
AS MonthsOnHand,
CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
THEN IV.MaxStockQty - ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) --* I.UserFld3
END
as QtyNeed, I.StdBinQty,
CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < I.StdBinQty
THEN I.StdBinQty
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) > I.StdBinQty
******This is the number I need rounded *****THEN ROUND(IV.MaxStockQty - (IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / I.userfld3), -2)))
END
AS OrdQty
from timwhsepurchprodln WPL
INNER JOIN TAPvENDOR v ON WPL.PrimaryVendKey = V.VendKey
INNER JOIN timPurchProdLine PPL ON WPL.PurchProdLineKey = PPL.PurchProdLinekey
inner join timInventory IV ON PPL.PurchProdLinekey = IV.PurchProdLinekey
INNER Join timBuyer B ON IV.BuyerKey = B.BuyerKey
INNER Join timBuyer BV ON V.BuyerKey = BV.BuyerKey
INNER JOIN timItem I ON I.ItemKey = IV.ItemKey
INNER JOIN timItemClass IC ON I.ItemClassKey = IC.ItemClassKey
INNER JOIN timWarehouse W ON W.WhseKey = IV.WhseKey
INNER JOIN timItemDescription D ON I.ItemKey = D.ItemKey
INNER JOIN timItemUnitOfMeas IUOM ON I.ItemKey = IUOM.ItemKey
INNER JOIN tciUnitMeasure UM ON IUOM.TargetUnitMeasKey = UM.UnitMeasKey
LEFT JOIN
(SELECT ItemKey, SUM(QtyOnHand) AS QtyOnHand
FROM timWhseBinInvt
GROUP BY ItemKey) BI ON BI.ItemKey = I.ItemKey
where IV.WhseKey = 22
and I.Status = 1
and ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
order by VendID
I have the following sp which is appending records into my table. However the values appended are being round up eg
SC_PrimaryPupilPrice is 1.5
but when it is inserted into the sql table it is 2
The field in the sql table is numeric.
CREATE PROCEDURE spSM_AddWeeksandMealPrices
@dteWeekEnding datetime
as
INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)
select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO
I have a problem...Data transformation rounds or truncate decimals!I have an ODBC source in witch is a table with float numbers (ODBC Driverpublish it as float).If I build a query form access or from excel with Query Analizer, I see alldecimal places, but when I try to insert data using DTS, float numbers willbe converted to its integer values.A "select * from table", with table ODBC table, gives integer value whenrunned from DTS to transform data from ODBC to MS-SQL Server table, andgives float values from Access or other tools.Where I can fix my problem?Thanks.Giorgio
View 1 Replies View RelatedWhen ISELECT CAST(96.58 AS DECIMAL(10 , 0)), it returns 97.When ISELECT CAST(575629 / 54 AS DECIMAL(10 , 0)), why it returns 10659? Itshould return 10660, right?What am I missing?Thanks,Faye Larson
View 1 Replies View RelatedPerhaps someone can settle an arguement for me ?I have a set of data that I need to group together. SQL Script below.CREATE TABLE [dbo].[CommTransactions] ([ID] [id_type] NOT NULL ,[TransactionID] [id_type] NULL ,[ClientID] [id_type] NULL ,[AccountCode] [varchar] (10) NULL ,[Amount] [float] NULL ,[CreateDateTime] [datetime] NULLFor the records I want to group the following applies.The ID is unique and distinct.The TransactionId is the same.The ClientId is the same.The AccountCode is different.The Amount will be the same.The CreateDateTime field is different by a few milliseconds.I want to create a single line showing two account codes in differentfields. i.e. Staff and Manager (where their ID is the account code).These can be entered in any order in the table mentioned.The problem I have is I need to link two records together (that's theproblem in it's most simplistic terms). However, there may beadditional records with the same TransactionId, ClientId, AccountCodeand Amount, but happened at a slightly different time. It could bedone on the same day.Now, the arguement is that we can group using the CreateDateTimefield. I argue that we can't as it will show down to the millisecondand any rounding will not always allow for a match. If we added thematching records once per day, then I can extract the date and groupon it, but if more than one group is added per day, then this wouldcause the logic to fail.So, are there any reliable methods for grouping date/time fieldsreliably if there is a small difference (I suspect not)?Is there anything I have missed ?Any help or suggestions would be appreciated.ThanksRyan
View 4 Replies View RelatedI have 2 record with same value A and B
A = 150.68273
B = 0.002000
1st Record A+B = 150.68473
2nd Record A+B = 150.68473
In the report column both of the result added up will show as 150.68, format code= N2
Now the diffrence is when i do the subtotal.
It gives me Diffrent value when i use
SUM(Round(Fields!ACCINT.Value + Fields!INTADV.Value,2)) = 301.36
and
SUM(Fields!ACCINT.Value + Fields!INTADV.Value) (format code= N2) = 301.37
why?
Hi All,
Is there a way in T-SQL using the round command to always round up regadless of the value.
For example:ROUND(normal_hours * pay_rate * 52, - 3) AS Expr3
normal_hours = 36
pay rate = 23.64
weeks in year = 52
(36 * 23.64) * 52 = 44,254.08
It rounds to 44,000. I want 45,000. Is this possible. Am I overlooking the obvious?
Thanks in Advance and Happy Holidays!!!!!!!!Adam
I'm trying to manipulate the data in my Service_2 column to round off the Estimated_Miles to the closest 5K miles. I tried the round function (Round(EstimatedMiles, -4)) but this does not exactly give me the results I want. Below is my result set:
Row_ID
Service_2
Estimated_Miles
1
40000
44012
2
50000
46124
3
120000
121512
4
30000
31857
5
10000
7547
6
80000
79500
And below is what I'd like to have
Row_ID
Service_2
Estimated_Miles
1
45000
44012
2
50000
46124
3
125000
121512
4
35000
31857
5
10000
7547
6
80000
79500
The range of data I'm working with is pretty broad meaning my Min & Max Estimated_Miles is between 0 - 1,000,000. In essence, I would like to say is if the estimated mileage is between 0-4999 then Service_2 = the closest 5K miles otherwise round to the closest 10K miles. Any help is truly appreciated. Thanks
I am trying to round this data, but can't find a good resource on this. If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE())
* employee.emp_accrual_rate - SUM(ISNULL(request_1.request_duration, '0')), .5) Any help would be greatly appreciated. A link to a good reference on rounding would help too.Thanks in advance!!!
I have a field in my SQL Server 2005 database of type numeric(18,3)In code, I treat the value as decimalWhen creating my command parameters, this is how I'm declaring them:prm.SqlDbType = SqlDbType.Decimal;prm.Precision = (byte)int.Parse("18");prm.Size = int.Parse("0");prm.Scale = (byte)int.Parse("3");Inserting a number like 5.687 is rounding to 6.000 anyone know why it is doing that?
View 5 Replies View RelatedI have a strange problem: Stored procedure to feed a report.The SP created a temp table and one of the fields is Decimal(8,1)The sequence of building this field is: Insert integer from a table Update the field: this is a simple division formula, the result could be a decimal and I want it upto 1 place only: Update #tempTable Set theDecimalField = IntegerField/IntegerFieldFinal SP that puts all together is just a select * from #tempTable.After verifying the math, one of the records should should give 1.6 in the DecimalField, the math being 40/24: I get a 2.0 instead.What am I missing? This is literally driving me crazy.
View 3 Replies View RelatedSQL Server seems to round this down:
AVG(DATEDIFF(d, startdate, enddate))
I've tried using ROUND(AVG(DATEDIFF(d, startdate, enddate)), 0) but it still rounds down.
For example, if the result is 1370.758, I want it to round to 1371.
Am I missing something?
Thanks.
This give me 6 digits to the right of the decimal point - can round it to 2???
CONVERT (decimal(18 , 2), 1.0 * NULLIF (vVotesR, 0) / NULLIF (vVotesR + vVotesD, 0) * 100)
I have a question about rounding and converting to percentage and adding in the '%'..This is my original code
SELECT * FROM
( SELECT Baby,
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue
FROM NewBorn
WHERE Category = 'Boy'
[code]....
But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue
But it prompt me the error 'Error converting data type varchar to numeric.'
A B C D
0.22%0.29%0.11%0.32%
I am trying to incorporate a few columns into a view that each shows a certain value based on a logged datetime specific to the value. Unfortunately, the logtimes are accurate down to milliseconds, and each value has it's own logtime. (They're suppsoed to log at midnight, but sometimes log a few seconds early or late).
I want to be able to round up to 00:00:00 if it's 23:59:59 and down to the same time if it's 00:00:01. I can't very well just drop the time component because if a device logged at 00:00:01 on Aug 4 for the Aug 3rd average, and 23:59:59 for the Aug. 4rd data, then I'd have two Aug 4th values and zero Aug. 3rd values.
Additionally, I need to keep this in a datetime format for reporting purposed in Crystal Reports.
Am I asking too much?
SQL Server 7.0The following SQL:SELECT TOP 100 PERCENT fldTSRID, fldDateEnteredFROM tblTSRs WITH (NOLOCK)WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',102))AND(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))returns this record:fldTSRID: 4fldDateEntered: 24/11/2003Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?I tried decrementing the second predicate by seconds:(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))returns the record, but(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))does NOT.What is happening here?Edward============================TABLE DEFINITION:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTSRs]GOCREATE TABLE [dbo].[tblTSRs] ([fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerID] [int] NOT NULL ,[fldWorkshopGroupID] [int] NULL ,[fldSubjectID] [int] NULL ,[fldReasonID] [int] NULL ,[fldFaultID] [int] NULL ,[fldContactID] [int] NULL ,[fldMileage] [int] NULL ,[fldFirstFailure] [smalldatetime] NULL ,[fldNumberOfFailures] [int] NULL ,[fldTSRPriorityID] [int] NULL ,[fldTSRStatusID] [int] NULL ,[fldAttachedFilePath] [char] (255) NULL ,[fldFileAttached] [smallint] NOT NULL ,[fldFaultDescription] [ntext] NULL ,[fldFaultRectification] [ntext] NULL ,[fldEmergency] [int] NOT NULL ,[fldDateEntered] [smalldatetime] NOT NULL ,[fldEnteredBy] [int] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
View 8 Replies View RelatedHi, I've just started SSR2005 this week, and I'm unsure of how to round down a number in my expression. My code is:
=reportitems!TotalSeconds.Value Mod 60
Basically, the total seconds could be coming in as 74.564, but I want the remainding seconds after dividing by 60. In other words, I would want to return 14, rather than 14.564. I have noticed that if I put N0 in the format, it rounds it up. Also I'm not sure how to use the round function, and it would appear to round up (?).
Can somebody help please?
Thanks, Dan.
Hi
Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?
Thanks,
Neil
Hi, I'm having a problem with unintended rounding of decimal values which I'm retrieving from a SQL Server 2000 table.
The table contains a decimal column defined as 9(16,8), and which contains values very close to 1, such as 0.9996.
The problem is that when the query executes from within our application, the recordset contains 1, whereas we were expecting to receive 0.9996. As you'd expect, this is causing problems in our app.
Our app is a legacy VB6 application connecting to SQL Server 2000 via Microsoft DAO 3.6 (dao.dll, 3.60.3706) using the SQL Server 2000 ODBC provider (sqlsrv32.dll, v2000.85.1117). Platform is Windows XP, SQL Server is running remotely.
Any help with this would be greatly appreciated.
Cheers,
Mark