I 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/IntegerField
Final 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.
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
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;
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
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.
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
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
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
When 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
Perhaps 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
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?
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.'
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.
Hi, 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 (?).
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.
I am getting this value "47670.5519699449" in a text file for one column. First I am reading as string data type dt_str from text file. Then I am converting the data type from string (DT_STR) to double.
I had used data conversion transformation and converted the data type to "double-precision float(DT_R8)".
After that when I see the results for that converted column I am seeing only this value "47670.551969945". It is getting rounded.
But i want the total value without rounding.
In .net I did the same thing by using double data type. It was giving correct results.
Depending on parameter I am either rounding the sum of sales or the sum of fees. This works beautifully. Problem is when I copy this to the total row it is not the total of the column. They are slightly off. I even tried naming the cell and then creating a calculated field in the total line to make sure that I am adding the values of that cell. =sum(ReportItems!MyNamedCell.value) But this crashes VS everytime and it has to restart. Does anyone have a suggestion please.
Hi, Im having a decimal value of 1.24 im rounding off the value to 1 precision ex:- select round(1.24,1) the output its returning is 1.20 i want to get only 1.2 can anyone suggest on this??
