An Average Formula

Posted on 2006-05-10
Last Modified: 2012-05-05
Objective: To write the required formula with correct and precise results:

Below is the formula for a desired output

    ------------------------------------ - 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

Question by:rhservan
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 17

    Accepted Solution


    (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

    Author Comment

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

    Expert Comment

    Can you test wish each of operation

    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)

    then add Str function if you want

    select STR(str,n1,n2)


    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)

    Author Comment

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

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    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 ( 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.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now