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) -
[code]...
View 5 Replies
ADVERTISEMENT
Oct 25, 2015
I have an equation
( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(1000000, 0) ) ) - 1 ) * 100 AS FibreScrapFactor
This works for the example order that I am looking at. However if i take out the where clause (so i have all orders) i get divide by zero error.
If I limit the / 1000000 to 10 which i then have
( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(10, 0) ) ) - 1 ) * 100 AS FibreScrapFactor
This works for all orders, but is incorrect I need to divide by a million. How can I get this to work?
View 3 Replies
View Related
Jun 18, 2015
My Code is
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.
View 4 Replies
View Related
Oct 9, 2015
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
How I can check it.
View 5 Replies
View Related
Jun 9, 2015
When I am doing the divide all values are showing Zero.
DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample
and also I want to show the QTR with %, for example, 66.57%
View 6 Replies
View Related
Jul 7, 2015
I have this rows as result of my query:
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*
[Code] .....
View 8 Replies
View Related
Nov 19, 2015
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,
[Code ....
View 6 Replies
View Related
Feb 24, 2006
Hello,
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?
ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
drop table #MaxArchiveDates
DECLARE @MaxArchiveDate INTEGER
, @EdgeModelID INTEGER
, @DefinitionID INTEGER
SELECT ArchiveDataDate = MAX(ArchiveDataDate) INTO #MaxArchiveDates FROM intldbArchive.dbo.GOModelUniversesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecurityGroupModelValSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecuritiesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOUniverseSecurityMembeSaveD
SELECT @MaxArchiveDate = MIN(ArchiveDataDate) FROM #MaxArchiveDates
SELECT @MaxArchiveDate
SELECT ModelID = today.ModelID
, RelativeUniverseID = today.RelativeUniverseID
, SecurityID = today.SecurityID
, ModelValueT = CONVERT(decimal (8, 4), today.ModelValue)
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
FROM OptMod.dbo.GO_SecurityGroupModelValues today
JOIN intldbArchive.dbo.GOSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = @MaxArchiveDate
Thanks in advance!!!
sqlnovice123
View 1 Replies
View Related
Feb 28, 2006
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
(((cs_acd_trk_t + cs_acd_ext_c)/(cs_acd_trk_c + cs_acd_ext_c)) + ((cs_t_acd_wrk)/(cs_acd_trk_c + cs_acd_ext_c))/(cs_acd_trk_c + cs_acd_ext_c))
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
View 2 Replies
View Related
Sep 27, 2006
In the following expression, the query executes with no errors (there are sometimes 0's and nulls in IRON and ENERGY):
SELECT * FROM gff2vg
WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)
order by IRON/ENERGY desc
but when TOP is added there is a divide by zero error. Is there a way to avoid this?
SELECT TOP(64) * FROM gff2vg
WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)
order by IRON/ENERGY desc
Thanks.
View 7 Replies
View Related
Jan 19, 2007
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:
= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)
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
View 14 Replies
View Related
Jul 31, 2006
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
View 2 Replies
View Related
Sep 5, 2001
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.
Thanks,
Ruaidhri.
View 1 Replies
View Related
Oct 31, 2013
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.
View 5 Replies
View Related
Nov 6, 2013
I have some SQL scripts for SQL Views and getting divide by zero error. I am using
COALESCE(Field1,Field2,1)/COALESCE(Field3,Field4,1)
This function can avoid NULL but how I can avoid 0 ?
Is there any function in SQL that can take care both NULL and 0 and replace it with 1.
View 5 Replies
View Related
Dec 3, 2007
How can i handle this kind of error in my execution of my storedprocedure? Error:Divide by zero error encountered.
thanks!
Funnyfrog
View 3 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
May 24, 2007
I am using iif which works just fine in Access to prevent divide by zero errors but this:
=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")
Fails
As Does:
=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)
Why? How do I get around this?
View 4 Replies
View Related
Oct 21, 2014
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.
View 6 Replies
View Related
Apr 15, 2015
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.
=IIF(Sum(Fields!PriorQty.Value) = 0.0, 1,(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value)) / Sum(Fields!PriorQty.Value))
View 10 Replies
View Related
May 8, 2008
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?
Code Snippet
=IIF(Sum(iif(Fields!Column_Text.Value = "Total", Cdbl(Fields!Period_1.Value), 0.0))=0,"0%",
Sum(iif(Fields!Column_Text.Value = "Total", Fields!Period_1.Value,nothing))- Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))/ IIF(Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing))=0,1,Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))
View 1 Replies
View Related
May 3, 2006
Hi, all here,
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?
Thanks a lot in advance for any guidance.
View 5 Replies
View Related
Jan 7, 2004
Hey all,
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
Maybe someone can help me
Cheers Wimmo
View 4 Replies
View Related
Mar 19, 2015
I'm getting this error:
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.
this is where i'm getting the error:
CASE WHEN Claims.ClaimTotal > 0 then (ISNULL(Claims.ClaimTotal,0) / ISNULL(TotalPrem.TotalPrem,dbo.TrnPolicy.TotalDue)) else 0 END AS LossRatio
View 3 Replies
View Related
Aug 31, 2004
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
View 4 Replies
View Related
Sep 2, 2007
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?
Best regards
Capgemini Norway
View 1 Replies
View Related
Mar 17, 2008
the query is below ;
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."
View 3 Replies
View Related
Sep 3, 2015
i am getting error for the mdx query fortotal profitis there any other way to write??????
View 2 Replies
View Related
Jul 27, 2001
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.
View 1 Replies
View Related
May 1, 2008
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
View 8 Replies
View Related
Jun 5, 2008
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?
View 5 Replies
View Related
Mar 8, 2006
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.
View 3 Replies
View Related
Feb 12, 2007
Hi,
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.
Thanks for your help.
View 3 Replies
View Related
