Eliminate Division By Zero

Jul 31, 2007

This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.

Any suggestions on how to improve this?


SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT

View 14 Replies


ADVERTISEMENT

Division In T-SQL

Feb 27, 2004

Hi,

Can anyone tell me why this doesn't return any decimals?


declare @f float
set @f = 6 / 18
print @f


All i get is 0... Is there something obvious i've done wrong?

TIA

View 3 Replies View Related

Division

Aug 9, 2000

Hi All

I am trying to devide 24 by 30 result it gives 0, but I wouldlike to get 0.8

how can I achieve this

I will apprecite your help, thank you

View 1 Replies View Related

Division By Zero Again

Mar 5, 2003

Hi all,

I have a prob with the following code...
in that the value of the amount of the year 2001
is 0 so it is showing me division by zero prob
can any one change the code and let me know the details


declare @year1 int
declare @year2 int
declare @month int

set @year1 = 2002
set @year2 = 2001
set @month =9

select
case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0
else
(
(sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) -
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end)
)* 100 end as Percentage,
from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site

Thanks

View 1 Replies View Related

Division Help

May 1, 2007

I'm very new to sql server and still don't know the in's and out's I have this:

Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END)/ Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Per_Quoted

I keep on getting 0, anybody have any ideas?

View 6 Replies View Related

Eliminate Transaction Log

Apr 22, 1999

The Transaction Log which I'm working is so loaded. I trucated it, but isn't enough, is still so big.

What can i do for clean the log and have free space again?

View 3 Replies View Related

Eliminate Duplicate

Jul 29, 2003

Table A

JobNoClaimShipType
A1100I
A1200II

Table B

JobNoCost
A150
A1100

Result Expected


JobNOCost Claim Shiptype
A150 100I
A1100 200II

Hi all,
i've given a table structure with data
and the expected result .
I want to establish it in SQL server (7.0)
If i establish the inner join i get 4 rows (2*2)
Please let me know how to get the result
Thanx in adv
Tarriq

View 1 Replies View Related

Help Eliminate Dupes

Nov 30, 2006

I am VERY new to SQL and I am having a heck of a time biulding a script to find and remove duplicate entries.

Here is the table structure.


CREATE TABLE [dbo].[SecurityEvents](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone.


select distinct * from dbo.SecurityEventsTest where recordnumber IN
(select recordnumber from dbo.SecurityEvents)
order by recordnumber

Could someone help??

Thank You,

John Fuhrman
http://www.titangs.com

View 9 Replies View Related

Eliminate Time

Mar 12, 2008

Hi,

In my query where clause I am using between to get data. Because of time in the data I need to eliminate that and compare, how can I eliminate. my where clause is as below. due to that my query performance is falling down. please help.

CONVERT(DATETIME, CONVERT(CHAR(20), OpportunityDate,110)) BETWEEN CONVERT(CHAR(20),@FDate,110) AND CONVERT(CHAR(20),@TDate,110)

Thanks

Sreenu

View 7 Replies View Related

Eliminate Repeating ID

Mar 17, 2008

Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,

for ex., i have given an example.,

create table Example (ID int, Name varchar(30))

INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')

select * from Example.,

i will get.,

ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush



.....
but i need like

ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush

I dont want repeated ID., How can i do that.,

View 14 Replies View Related

How To Eliminate Double Row

Feb 8, 2008



'My table' is below with double row

lot value date

2 300 3/2/06
3 200 6/5/05
4 100 5/21/07
5 340 6/23/06
2 250 4/3/06

My query such as
SELECT lot, value, date
FROM my table

How can I eliminate 1 row of lot 2 and chose the recent date only?

Thanks for your help
Daniel

View 3 Replies View Related

Division By 0 In Query

Dec 17, 2007

In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X).
The problem is that X can be 0 sometimes and then I get an error.
How can I prevent errors like this for the case of X=0?
In access I would use IIF function, but it doesn't appear in SQL SERVER views.
Thanks.

View 5 Replies View Related

Decimal Division

Mar 7, 2005

Hi,

I'm trying to divide two decimal(19,4) numbers but it keeps giving me a Divide by 0 error.

- 10698.25 / 76782.11 = -0.13 (but I get a Divide by 0)

I looked it up in the Sql Server docs, does the Divide by 0 error also cover Stack overflows?

If so, how do I get around this?

Many thanks,
Stuart

View 1 Replies View Related

Division By Zero In Views

Mar 7, 2008

hi
in a view, i create a field like field1/field2.
in case that field2 is zero, i've got division by zero error.
how can i change the value to 0 when field2 is 0 for avoiding get error.

View 1 Replies View Related

Basic Division In SQL

Nov 20, 2007

I want to count the rows in two tables and then give a percentage as a result.

Something like:

SELECT Count(*) / (SELECT COUNT (*) FROM Table2) FROM Table1

Just not quite sure how to do this.


*Thanks*

View 14 Replies View Related

DIVISION ALWAYS RETURNS 0

Dec 11, 2007

It is common for me to need to create ratios from data in my database such as

SELECT
( list_value / sale_price ) as ratio
FROM
values


The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?

doco

View 5 Replies View Related

Help With Division In Query

Dec 14, 2007

Hi,

I have a table in which i have two colums say discription and counts.
the table has 10 rows.
This table is created by extracting data from other table means its not a table that exist in system.

Now in my last row i want discription as '%mailed' and count as row1/row2

can u tell me how to do that?

View 2 Replies View Related

Need Help With SQL Command / Division

Mar 15, 2008

SELECT [Record Number], Date, Car, Miles_Start, Miles_Stop, Gallons_FillUp, Miles_Stop - Miles_Start AS Miles_Total, Miles_Total / Gallons_FillUp AS MPG
FROM Sheet1_Table
ORDER BY Date DESC, Car

Hi,
I have Visual Studio 2008 Pro / Using VB 9.0 / SQL Server Express 2005. I am making a Miles per gallon calculator for my dad. All Fields /Cells are populated in two records excepting the Miles_Total & MPG. The Miles_Total Comes out fine but... the MPG does not . Any help with this Query would be greatly appreciated!
Jeff L

View 3 Replies View Related

Division By Zero Problem

Nov 15, 2007

I've got the following SQLSELECT count(*) FROM tablea AJOIN tableb B ON ..etc..WHERE a.string_val = 'test' ANDb.divider 0 ANDa.number 0 AND(a.number / b.divider 0)The b.divider value can be 0, but still I get the division by zeroerror message.I guess the reason is that the database performs the where statementsone at the time?So when performing the division a.number / b.divider it could get 0 inthe divider even if b.divider 0 is also part of the wherestatement??My question is then.. How can I work around this problem? Changing thedatabase to set b.divider always 0 is not an option

View 2 Replies View Related

UDT For Division Methods

Sep 4, 2007

Hi,

I plan to implement UDT for division methods.


The following fragment TSQL with zero check:


DECLARE @a int, @b int


SELECT



CASE WHEN @b = 0 THEN NULL

ELSE @a / @b
END



Clr UDT may look like this and script will be shorter.


DECLARE @a int, @b int


SELECT @a Type :: divide(@b)







Is that better to use UDT ?

Anyone have try this before?

View 8 Replies View Related

Decimal And Division

Mar 5, 2008

Could anyone explain the difference between the following 3 queries?

select CONVERT(decimal(38,25),100) / CONVERT(decimal(38,25),1750)

select CONVERT(decimal(19,8),100) / CONVERT(decimal(19,8),1750)

select (100.000/ 1750.000).

I would expect the first one to give me the most accurate result but the second query with a lower precision and scale returns a result with a greater scale.Thanx.

View 3 Replies View Related

Division In SQL Results In 0

Jan 9, 2006

division in SQL results in 0
Posted on:  01/09/2006 09:20:13

  





declare @x int, @y int, @f float
set @x = 100
set @y = 2000
set @f = @x / @y
print @x
print @y
print @f


in the above code @f prints as 0 instead of 0.05
how would i correct that?

View 6 Replies View Related

Analysis :: How To Sum A Division Using MDX

Nov 9, 2015

I want to do something simple but I don't know how to do it using MDX. I searched a solution on the web but I found nothing. I want to SUM the result of the division of two measures. I tried using the WITH MEMBER Measures.sum_divided AS SUM(x/y) but the result in the cells are all #Error.

View 12 Replies View Related

Eliminate Duplicates From Datasource

Mar 26, 2007

Hi All,
I have a SP, which i run inside a for loop.
I am running the SP for all the products in a listbox.
So for each product i am having the feature extracted through the SP
But some features are the same for 2, 3 products.
So in the datatable, i am getting the featrues repeated.
IS there any way to eliminate the duplicates from datatable, from server side?
Hope i am not confusing.
Eg: product1 ---  test1, test2, test3
product2 --- test2, test4
so the datatable has -- test1, test2, test3, test2, test4
-- i have to eliminate one test2 from this.
Any ideas???
Thanks

View 1 Replies View Related

Trying To Eliminate Secondary Log File

Mar 14, 2003

I'm trying to run a set of DBCC commands to empty and then delete a secondary log file; however, no matter how large I make the primary log file it won't empty the secondary file. Any suggestions?

DBCC SHRINKDATABASE (VE, 10)
GO
alter database VE modify file (name= VE_log,size = 1200)
GO
dbcc shrinkfile (VE_log2,EMPTYFILE)
GO
Cannot shrink log file 3 (VE_Log2) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
17 3 86848 128 86848 128

--alter database VE remove file VE_log2
--GO

View 1 Replies View Related

How To Eliminate Space Entries...?

Jul 23, 2007

Hey,
I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them.
When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..

Thx in advance..

View 4 Replies View Related

Query To Eliminate The Records

Apr 7, 2008

Hai

I have written one select statement and it returns the following records.

SELECT a.Name, b.address FROM tbl1 a INNER JOIN tbl2 b ON a.id=b.id

Name address
AAA XYZ
BBB
CCC
DDD

My requirement is to display only if address fields contain values.
For Eg. I want to display AAA XYZ record only. How can I write a query.

Help me.

Kamal..

View 3 Replies View Related

How To Eliminate The Null Values

Nov 20, 2006

hi, i have null values in my table , i want to eliminate the null values.
ie
this is my query
select p_type from process_general
output:
1.BSB HEATER PACKAGE
2.

so in my output one data and one null field is there. so i want to show output with out that null field, becos i am filling this datas in my combobox.so i need with out null field.please give me query for this,pleaseeeeeeeeeeeee

View 3 Replies View Related

Eliminate Duplicate Record(s)?

Jun 22, 2007

Hey Again,

I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:

SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID

FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID


This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).

Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.

View 1 Replies View Related

How I Do To Eliminate Duplicate Rows?

Apr 11, 2007

Hello,
I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure:
Id_type, number_type, date, diagnosis, sex, age, city

After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear.

How I can do it??

Thank you very much.

Regards,
Angela

View 7 Replies View Related

How To Eliminate Duplicate Data

Sep 20, 2006

I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?

View 5 Replies View Related

Division Operation On Two Columns.

Mar 24, 2008

Hi to all,
Im using SQL Server 2000.
If i divide two integer column values, the result i am getting is integer. i.e
Update tblStudents SET Percentage=(AttendedClasses/TotalClasses);
AttendedClasses,TotalClasses are two column  names(Integer). I hav declared Percentage column as float.
The result im getting is integer value..
I want the actual float value(i.e Percentage)
How to Resolve this??
Help me with this issue..
thanks in advance.
 
 

View 1 Replies View Related

SQL Server Division With No Decimal

Aug 20, 2004

Hi - I have what seems like a simple problem and I cannot find an answer to it. I'm hoping someone here can help.

I want to divide 2 numbers but have the answer come back with no decimal places. I don't want to round up or down..just give me the whole number and drop the decimal places. For example:

Select units/60 as answer


And units = 280. I want the "answer" to be 4 and NOT 4.66.

Any ideas?

I'm on SQL server version 8.0.

Thanks in advance!
Caitlin

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved