Hi Everyone,
I noticed that when there are two number being divided such as: 1/2 or 10/4 in SQL Server 2005 Reporting Services, they do not round up.
For example:
= 1/2 = 0.5 = 1 ... Instead it rounds down to 0
= 10/4 = 2.5 = 3 ...Instead it rounds down to 2
How can I make my report round properly? I am using the Round function in the expression.
Thank You
-Same
how to round the nearest value after round of decimal and return integer.
declare @data decimal(18,2)
set @data = 5.55
-- output
select '5'
set @data = 5.58
-- output
select '6'
Haiii..
I save number in database as varchar, but i want to call it back and
convert as double/int. Can it be done
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
Working on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
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
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'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