Solved

# An Average Formula

Posted on 2006-05-10
821 Views
Objective: To write the required formula with correct and precise results:

Below is the formula for a desired output

1623col
------------------------------------ - 110
((1600col/480) + (1603col/470)) - 1622col

- 1600col, 1603col, 1622col & 1623col are all columns
- Each column is required to be run as an average
- Each division needs nullif  for 0
- AVG is used to total hourly reports and produce an average output of those reports

Current formula: below (not working) I originally worked out the formula in long hand and it doesn't seem to hold the parantheses.

STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0)
/ NULLIF ((AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0))
- (AVG(dbo.Table_II.[1622col]) - 110), 8, 2) AS AVG1

0
Question by:rhservan

LVL 75

Expert Comment

select STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0) )
/ NULLIF (AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0)
- (AVG(dbo.Table_II.[1622col]) - 110), 8, 2) AS AVG1
0

LVL 17

Accepted Solution

select
STR(
(NULLIF (AVG(dbo.Table_II.[1623col]), 0)
/ (NULLIF (AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0)
- AVG(dbo.Table_II.[1622col]))
- 110)
, 8, 2)
AS AVG1
0

Author Comment

I wish it were that simple.  I attempted your parenthese change and it would not run. "select" is a given.
0

LVL 17

Expert Comment

Can you test wish each of operation
e.g

1623col <==> NULLIF (AVG(dbo.Table_II.[1623col]), 0)
1600col/480<==>NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)
1603col/470<==>NULLIF(AVG(dbo.Table_II.[1603col] / 470),0)
1622col<==> NULLIF (AVG(dbo.Table_II.[1622col]), 0)

select (1623col/(((1600col/480) + (1603col/470)) - 1622col)-110

replace with above value we have

select (NULLIF (AVG(dbo.Table_II.[1623col]), 0)/(((NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)) +
(NULLIF(AVG(dbo.Table_II.[1603col] / 470),0))) - NULLIF (AVG(dbo.Table_II.[1622col]), 0)
)-110

then add Str function if you want

select STR(str,n1,n2)

so

select STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0)/(((NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)) +
(NULLIF(AVG(dbo.Table_II.[1603col] / 470),0))) - NULLIF (AVG(dbo.Table_II.[1622col]), 0)
)-110,8,2)
0

Author Comment

HuyBD your first response was correct already. thanks for the good eye on that one.
0

## Featured Post

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.