T-SQL (SS2K8) :: Rounding Off A Percentage Result?
Apr 30, 2014
how to remove the trailing chars from the end of a percentage calculation.
For instance:
SELECT
CAST(NULLIF(t.SLAHours,0) - t.TotalDownTime as REAL) /
CAST(NULLIF(t.SLAHours,0) as REAL) * 100 as [%Availability]
FROM DBName.dbo.TableName t
This gets me '99.00294' (example)
I may have gone about this totally the wrong way - but I just want to trim the unwanted chars from the end - I've managed to trim some off by using REAL but I only need the basic percentage number.
I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).
My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.
The first snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex AS SELECT PP.PortfolioID AS PortfolioID, PP.CreateDate AS CreateDate, (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex, CASE WHEN PPI.CloseIndex IS NULL THEN 100.00 ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) END AS CloseIndex, (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex, PP.Volume as Volume FROM dbo.PortfolioIndex PPI INNER JOIN dbo.PortfolioPerformance PP ON PPI.PortfolioID = PP.PortfolioID AND PPI.CreateDate = PP.PrevDate GO
and it's result set: PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume 102/20/2004781.11774.17769.53527896 112/20/2004757.97750.36742.93605740 122/20/2004509.92501.72494.854180516 132/20/2004988.23980.65973.58632337 142/20/20041283.261269.571259.37416145
And the second snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex AS SELECT PP.PortfolioID AS PortfolioID, PP.CreateDate AS CreateDate, (CloseIndex * PP.AvgHighPriceRatio) AS HighIndex, CASE WHEN PPI.CloseIndex IS NULL THEN 100.00 ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) END AS CloseIndex, (CloseIndex * PP.AvgLowPriceRatio) AS LowIndex, PP.Volume as Volume FROM dbo.PortfolioIndex PPI INNER JOIN dbo.PortfolioPerformance PP ON PPI.PortfolioID = PP.PortfolioID AND PPI.CreateDate = PP.PrevDate
which returns a different result set: PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume 102/20/2004784.52774.17772.89527896 112/20/2004755.64750.36740.64605740 122/20/2004512.43501.72497.294180516 132/20/2004989.77980.65975.1632337 142/20/20041285.991269.571262.05416145
Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.
I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself
I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values for the percentage (also using round).
IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo Create Table #Redondeo (Orden int Identity(1,1),Valores money) Insert into #Redondeo Select 71374.24 Union Select 16455.92 Union Select 56454.20 Union Select 9495.18
When sizing products we use predefined size groups that the users can choose any or all of the sizes from. For example if i size group consisted of sizes (6,8,10) they could use all sizes (6,8,10) or just (6,8) or just (10) if required. Similarly, if a group consisted of (S,M,L,XL) they could choose to only buy (S,L). They cannot choose across groups, so would not be able to choose (6,S)
Once the required sizing is determined they then assign size mixes to the sizes to denote how much of the buy will be in that size. So for example if we had 3 sizes: (6,8,10) and they had the associated mixes (25%,25%,50%) that would mean we would buy 25% of size 6 and 50% of size 10. All size mixes must add up to 100% in total.
The users do analysis to determine what sizes they wish to buy and how much of it.
We also have a franchise portion of the business that have some predefined size mixes. They use the same base size groups as above, but the rule is that they can only use sizes that the particular product is being bought in.
So if the assigned franchise mix is S (50%), M (50%) and the main mix was S (100%) then the franchise mix would only be able to then have the S size.
We would then eliminate the sizes from the franchise mix and then to ensure that the franchise mix still adds to 100 we would then pro-rate up the franchise mix to give a new mix. To do this I divide one by the total the remaining size mixes to get a ratio and then multiple the mixes by this factor.
In the case above not be able to use the M size and would only use the S.This would be
-Total of remaining mixes, in this case only size S for simplicity 1 / 0.5 = 2
-multiple original mix by this factor 0.5 * 2 = 1
size S would now be 100% instead of 50%
The issue I'm having is that on occasion some of the totals are adding up to 100.01% because another one of the requirements is that it needs to be 4 decimal places (0.1015 would represent 10.15% in excel)
Here is a shortened version of the code with some test data:
With Item as( Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as ( Select 1 as Parent, 2 as child union all Select 1 as Parent, 3 as child union all Select 1 as Parent, 4 as child union all Select 5 as Parent, 6 as child union all Select 5 as Parent, 7 as child )Expected Result:
I have an issue while display the result in the required order. How to get the required output.
Code :
USE tempdb GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL DROP TABLE #VersionFormat_tbl CREATE TABLE #VersionFormat_tbl ( [FormatID] [smallint] NOT NULL, [Description] [varchar](50) NULL,
[Code] ....
Present output : fileExtension FormatID Description fileExtension versionFormatTypeId txt 1 Text txt 1 html 2 HTML html 1 xml 3 XML xml 1 pdf 4 PDF pdf 1 xls 5 Excel xls 1 doc 6 Word doc 1
I want to make a string from "TarLang.AppendText(String.Format("{1}" & vbCrLf, r("Source Language"), r("Target Language")))" and the result I get when I use that.
add the result into a new row in the datagridview
Dim CompanyID As String CompanyID = OrderNR.SelectedItem.ToString() SQL.ExecQuery(String.Format("SELECT snSrc.kod as 'Source Language', snTrg.kod as 'Target Language' FROM [teknotrans_dev].dbo.OpusOrderrow as ord INNER JOIN [teknotrans_dev].dbo.OrderVolvoLanguageName as snSrc
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
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
