1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

average for period (no weekend data in table)

Discussion in 'SQL PL/SQL' started by ecivgamer, Jun 13, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all, please find my code of calculating average value for month. How do I calculate average for period from 13.03.2011 untill 20.03.2011 (these dates are absent in table) ?

    Code (SQL):
    WITH t AS (
    SELECT to_date('01.03.2011','dd.mm.yyyy') AS mydate, 11960157 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('02.03.2011','dd.mm.yyyy') AS mydate, 12965902 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('03.03.2011','dd.mm.yyyy') AS mydate, 13939736 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('04.03.2011','dd.mm.yyyy') AS mydate, 14181189 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('09.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('10.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('11.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('12.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('14.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('15.03.2011','dd.mm.yyyy') AS mydate, 14275304 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('16.03.2011','dd.mm.yyyy') AS mydate, 15274796 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('17.03.2011','dd.mm.yyyy') AS mydate, 15431807 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('18.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('21.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('22.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('23.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('24.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('25.03.2011','dd.mm.yyyy') AS mydate, 15480730 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('28.03.2011','dd.mm.yyyy') AS mydate, 15038595 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('29.03.2011','dd.mm.yyyy') AS mydate, 15038595 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('30.03.2011','dd.mm.yyyy') AS mydate, 15038595 AS myvalue FROM dual
    UNION ALL
    SELECT to_date('31.03.2011','dd.mm.yyyy') AS mydate, 15038595 AS myvalue FROM dual
    )
    SELECT  SUM(adjusted_myvalue) / cnt
      FROM  (
             SELECT  (lead(mydate,1,last_day(mydate) + 1) OVER(ORDER BY mydate) - mydate) * myvalue adjusted_myvalue,
                     to_number(to_char(last_day(mydate),'dd')) cnt
               FROM  t
            )
      GROUP BY cnt
    /

    SUM(ADJUSTED_MYVALUE)/CNT
    -------------------------
                   14689262.9

    SQL>
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    This is how I get average of myvalue for period. How do I get average of myvalue and myvalue2 for period in one query?

     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    This code works fine, it returns proper average value for contragentid = 1. So, correct result is LUAHPER = 14996837,94

    But when I add values for contragentid = 2 (see commented strings), my statement returns incorrect result LUAHPER = 5497801,81

    How do I improve my code in order to calculate average for each contragentid?

     
  4. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Here is my solution:
    My problem is solved. Just for user's information and checking purpose I need to return all values and dates, including those absent in table. Hope someone can suggest me something, however it's not so urgent and important.