Transact SQL :: How To Get Percentahe Profit Without Error In Zero Divide
Nov 4, 2015
How can I get the value (pr_profit / pr_sales ) * 100, (cur_profit / cur_sales) * 100 for %profit. When I am giving zero devide error is coming . How can I give if pr_profit = 0 then % profit should be zero.
SELECT
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal Else 0 End) as cur_Sales,
SUM(CASE WHEN invdate >={d '2014-01-01' } and invdate <= {d '2014-11-03' } Then linetotal - linecost Else 0 End) as pr_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) as cur_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) -
SELECT Client,State, CASE WHEN Weight>=11 and Weight<=455 and Height>=110 and Height<=250 THEN isnull((cast(nullif(Weight,0)/power((NullIf(Weight,0)/100),2) as int)),0) else -1 end as BaseBMIScore FROM Test100
And I got following error:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
I have a work database where I implemented a table-valued function. One colleague of mine reported to me that this function gave a Divide by Zero error when executed with some specific values given to its arguments (there are a 15 arguments). Then I started debugging, and I introduced some exit points to the function before its end in order to detect the point where the error appeared, since I don't have access to the database server and I cannot use the debugging tools from remote, due to the network configuration of my office. I can only do attempts on the code to try to find a solution.
Since I didn't manage to get rid of this error, I decided to make a silly and desperate attempt: I put a RETURN statement immediately after the BEGIN of the function body, with the idea that the function should not raie any error if it exit immediately after its beginning, despite the fact that this results in an empty table in return.
The result of my attempt is that the Divide by Zero error is still THERE (!), even if my function looks like
ALTER FUNCTION [dbo][<myFuncName>](...parameters...) RETURNS TABLE (...table definition...) AS BEGIN RETURN END GO
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 a 100 7-7-15 a 100 8-7-15 b 200 7-7-15 b 200 8-7-15 b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 50 a 100 7-7-15 50 a 100 8-7-15 100 b 200 7-7-15 200 b 200 8-7-15 100 b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) : SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3, CASE WHEN TRAPR3 IS NULL THEN (CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
I have typed a query by combining multiple tables to show sum of TotalReceivedAmount by MRN. It also have payment mode which is paid by cash or credit card. Now the query shows the sum for MRN combining all the payment done by credit card and cash. How am I supposed to show the amount paid by credit card and cash separately?
Here is the query :
SELECT Distinct dbo.CA_Payment.MRN, dbo.CA_Patient.FirstName, dbo.CA_PaymentModeMaster.Description AS PaymentMode, CASE (dbo.CA_Payment.PaymentModeID) WHEN 2 THEN dbo.CA_Payment.CardNumber END AS CreditCardNumber, CASE (CA_Registration.PatientTypeID) WHEN 1 THEN dbo.CA_PatientTypeMaster.Description WHEN 2 THEN dbo.CA_DebtorMaster.DebtorName END AS Debtor,
When I execute the following query, I get a Divide by zero error. I believe the Divide By zero error is occuring in the following statement. How do I avoid the error?
I am writing a report that deals with time in seconds. I am trying to figure out the average handle time for each call. To Get this number i use 5 fields. the formula is
Which translates to ((Incoming Handle Time) + (Work))/(Number of Incoming Calls)
When it does the calculations some times the cs_acd_trk_c + cs_acd_ext_c (number of incoming calls) is zero.
I dont know how to get around this ... i tried to set that value to <>0 in my query but it threw my other numbers off for number of calls which is a different value on the report. Any Help on this would be super appreciated. Thanks, Mitch
I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:
I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:
IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value) I guess the 42 is cast to an INT inside the second IIF and the calculation works. What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that. Has anybody run into this problem? Is my workaround the recommended approach? -Larry
Hi Is there a way to prevent this error? I'm getting it from query line: 10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)),1) 'Atingimento Semestre' Thanks a lot
Some of my calculated members look like "######" in Excel and I believe it is because of divide by zero errors. I have tried right clicking on the table, selecting "Table Options" and "For error, values, show:" I have enter 0. This is not working. Does anybody know how to get rid of this.
I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.
I am calculating Average Number of patients per day per nurse by using below query
SELECT Nurse, COUNT(DISTINCT patientName) AS NoOfPatients, COUNT(DISTINCT patientName) * 1.0/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay] FROM Table t GROUP BY NURSE
but if ArrivalDate is Null I am getting below error saying that Divide by zero error encountered.
I have the following line in a select statement which comes up with adivide by zero error.CAST(CASE Splinter_StatusWHEN 'SUR' THEN 0ELSE CASE WHEN Sacrifice>=1THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/(m.Gross+m.Sacrifice)ELSE 0ENDEND AS Float)AS Bond2,The error happens on the section (m.Gross + m.Sacrifice) as this canequal zero and throws out the part of the calc that divides by it. Itis correct in some instances that it does so. The full SQL statementhas a large number of these expressions so I need a method I can applyto any line if possible.I know that it is mathmatically correct to error where this value iszero, but what I want to do is set the output of the entire expressionto zero if there is an error.Realistically an error such as this could happen at a few points inthe expression (or one of many others), so I need to find a way ofcatching any error in the expression and setting the return value to0. I thought of using a CASE statement, but wondered if there was abetter way of looking at this as the case statement would have tocheck each variation where it could throw an error.Any ideas ?ThanksRyan
Being one step removed from innumerate, I was wondering whether there was a more elegant way to avoid divide by zero error instead of trudging through a bunch of isnulls.
My intuition tells me that since multiplication looks like repeated addition, that maybe division is repeated subtraction? If that's true is there a way to finesse divide by zero errors by somehow reframing the statement as multiplication instead of division?
The sql statement that is eating my kishkas is
cast(1.0*( (ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/ ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)) as decimal(10,4)) TotalLossRatio
Is there a way to nucleate the error by restating the division? My assertion underlying this statement is that the a alias represents a premium paid, so between medical, pharmacy and dental, there MUST BE at least one premium paid, otherwise you wouldn't be here. the b alias is losses, so likewise, between medical, pharmacy and dental, there MUST BE at least one loss (actually, it just occurred to me that maybe there are no losses, but that would be inconceivable, but ill check again)) so that's when it struck me that maybe there's a different way to ask the question that obviates the need to do it by division.
I have SSRS 2012 and have a report with an expression shown below. I am getting an error of
"[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox133.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero."
Textbox 133 has an expression as shown below and I cannot figure out why I get the error because I thought the IIF would bypass divide by zero.
Does anyone know how to get over their dreadfull divide by zero error. I keep getting the error, i have changed this code so much. I need to get the percent increase from 2007-2008 ("Total") in this table grouping. I know im getting the error in some of the fields, because sometimes the total is 0 and sometimes the 2007 is 0. What do i do?
I have a question about profit chat (with parameters population, fixed cost, individual cost, revenue), so in what kind of cases or scenarios should we use the profit chart?
When i exec an sp it runs a sum, sometimes it is possible that there is a 0 value, it then returns an error Divide by zero error encountered How can i work around this error
My sp code is
CREATE proc CP_avgloss_rings @mID varchar(10), @startdate datetime, @enddate datetime as select ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) as avgloss from dbo.rings where machineID = @mID and convert(varchar,njdate,121) between convert(varchar,@startdate,121) and convert(varchar,@enddate,121) GO
Hi I am quite struggling to work out how to calculate profit in stored procedure. The problem is I have the table: share name / transaction date/ is it buy or sell / no of shares bought or sold /total cost
I need to calculate how much profit was made on sold shares The problem is that I need to always assume that you sell shares which were bought first first. So if I have in chronological order: tesco buy 50 £200 tesco buy 40 £100 tesco sell 55 £600 profit is costtobuy of 55 shares: 50 shares at £200 + 5 shares at (£100/40*5 =)£12.50 = £212.50 profit = £600 - £212.5 = £387.50 (too good to be true)
my plan was (if it is too confusing please ignore it and just read the part above)
1 to work out no of shares sold and total cost of sold shares so select sum(no of shares) as nosold ,sharename where transaction = sell and sharename= @sharename return noofshares select sum(totalsold) as totalcostsold , sharename where transaction = sell and sharename = @sharename return totalsold now i need to loop through buys to work out cost to buy of number of shares sold:
so i need to loop through a table which would have buy transactions only(share name, nobought, totalcost) and check what was the cost to buy of this number of shares: @nosold(from above table) @sharename
output totalcosttobuy = 0
if(nobought = nosold) totalcosttobuy = totalcostobuy + totalcost retrun else if (nobought>nosold)totalcosttobuy = totalcostobuy + (totalcost/nobought)*nosold return else if( nobought<nosold)totalcosttobuy = totalcostobuy + totalcost nosold = nosold-nobought (go back to beginning of if) where sharename = @sharename group by sharename
Well I need this if statement to loop through the rows in the table how can I do that? Then I can do profit = totalcosttosell - totalcosttobuy
I have a population of debitors, and I have been using profit charts in SQL server 2005 to decide which and how many debitors to contact in order to maximize revenue on my debitor portefolio. My question is: Is it possible to set the Revenue Per Individual to a dynamic variable(e.g. a percentage of the debitors debt) instead of a fixed revenue?
Select Model, Kota as [Çıkan Anket] , (Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model) as [Verilen Adres] , (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model) as [Donen Adres]
,
(Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model) as [Donen Anket] ,((Select cast(Count(*) as decimal(2,0)) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model ) / (Select top 1 (cast(Kota as decimal(2,0))) From dbo.Model Where Model = tt.Model))*100 as [Oran]
,
(Kota - (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model)) as [Kalan Anket] , ((Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model)- (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model)) as [Kalan Adres]
From
(SELECT Model, SUM(Kota) AS Kota FROM dbo.Model GROUP BY Model) as tt where tt.Model in('A3')
it's returning error message : "Divide by zero error encountered."
I have a field that divides one field by the sum of two others. However, when both of the two latter fields are 0's, I get a divide by zero error and the output halts. How do I get this so that for these records I put a NULL or UNDEFINED or something in there at least so I can see the rest of the output. Thanks.
I have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please
SELECT COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs, SUM(dbo.safety_obs_data.safe) AS TotalSafe, SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe, CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / (SUM(safe) + SUM(unsafe)))AS [% Safe], SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen, SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe, SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote, SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks, dbo.Employee.emp_user_id FROM dbo.safety_obs_data INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no WHERE (dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102)) GROUP BY dbo.Employee.emp_user_id
SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END)) = 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)) END AS Rspec_Vulgar FROM dbo.TekliveQuery
--- Note: How can I avoid the the error message if the dividend and the divisor are both 0?
When I specify a formula between Computed Column Specification, I have twozero values, getting Divide by Zero error, any idea how can I avoid this? Istill want SQL Server to display Zero if it is 0/0, is this possible in SQLServer database?ThanksJ.
In the BI developement, in a matrix i'm trying to avoid division by zero, but even if i test the field before the division, i've got the error everytime.
I put the folowing test condition in the field, but i've got an error.
Ex : iif(Turnover <> 0, Cost/Turnover,0)
But i've got error message when the turnover is 0.
