• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

An Average Formula

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
rhservan
Asked:
rhservan
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
HuyBDCommented:


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
 
rhservanAuthor Commented:
I wish it were that simple.  I attempted your parenthese change and it would not run. "select" is a given.
0
 
HuyBDCommented:
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)

So your query like
 
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
 
rhservanAuthor Commented:
HuyBD your first response was correct already. thanks for the good eye on that one.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now