This discussion is archived
14 Replies Latest reply: Sep 30, 2013 12:09 PM by n_shah18 RSS

sql calculation help needed.

n_shah18 Newbie
Currently Being Moderated

Hi all gurus.

I'm having problem where I need to calculate AR days.

formula  is (sales of prior 3 months/90)/Ar $ amount.= Days

 

Data is stored in two different table.

1.Monthly data table .

CURR_MO                 REVENUE_PRIOR_MO (in Sept they will enter what was made/achieved in AUG)

-------------------- --------------------

JANUARY                                 0

FEBRUARY                          1717631

MARCH                             1591972

APRIL                             1649450

MAY                               1662600

JUNE                              1667823

JULY                              1669651

AUGUST                         1709323

SEPTEMBER                   2006983

 

2) Ar $ value enter each week  in Balance sheet table

 

 

AR$            week#   period

25895701007-MAR-13
23762941114-MAR-13
24549191222-MAR-13
27670581331-MAR-13
26038311405-APR-13
27595901511-APR-13
23762941618-APR-13
23084761726-APR-13
24551421802-MAY-13
24985461910-MAY-13
24574992123-MAY-13
2461661.792230-MAY-13
23103462306-JUN-13
25121392413-JUN-13
22677582520-JUN-13
24975662627-JUN-13
25832082704-JUL-13
21956672811-JUL-13
22880982918-JUL-13
23227783025-JUL-13
23068783101-AUG-13
25137413208-AUG-13
23507283315-AUG-13
25548423422-AUG-13
26641163529-AUG-13
27439313605-SEP-13
25018863712-SEP-13
23154773819-SEP-13

 

 

I want to take sum of 3 months corresponding

Daily Rev.JuneJulyAug3-mo Total
     59,844       1,669,651       1,709,323       2,006,983       5,385,957
Daily Rev.MayJuneJuly3-mo Total
      56,076       1,667,823       1,669,651       1,709,323       5,046,797
Daily Rev.AprilMayJune3-mo Total
      55,556       1,662,600       1,667,823       1,669,651       5,000,074
Daily Rev.MarchAprilMay3-mo Total
      55,332       1,649,450       1,662,600       1,667,823       4,979,873
Daily Rev.Feb.MarchApril3-mo Total
      54,489       1,591,972       1,649,450       1,662,600       4,904,022
Daily Rev.JanFeb.March3-mo Total
      55,101       1,717,631       1,591,972       1,649,450       4,959,053

 

Highlighted daily values with be divided by Ar $ amount  depending on monthly daily rev.

 

Output should be like (roughly this not actually output)

ARDAYSWEEK_NUMBER
          47 10
          43 11
          45 12
          50 13
          48 14
          51 15
          44 16
          42 17
          44 18
          45 19
          44 21
          44 22
          42 23
          45 24
          41 25
          45 26
          46 27
          39 28
          41 29
          41 30
          39 31
          42 32
          39 33
          43 34
          45 35
          46 36
          42 37
          39 38
  • 1. Re: sql calculation help needed.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sorry, it's not clear what you want.

     

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    I am sorry frank

    I am using oracle 11.2 gr2

    table 1

     

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('MARCH    ',1,1591972);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('FEBRUARY ',1,1717631);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('JANUARY  ',1,0);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('APRIL    ',1,1649450);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('JULY     ',1,1669651);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('JUNE     ',1,1667823);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('AUGUST   ',1,1709323);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('SEPTEMBER',1,2006983);

    Insert into MO_SETTINGS (CURR_MO,COMPANY_ID,REVENUE_PRIOR_MO) values ('MAY      ',1,1662600);

     

     

    table 2

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2589570,1,10,to_date('07-MAR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2376294,1,11,to_date('14-MAR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2454919,1,12,to_date('22-MAR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2767058,1,13,to_date('31-MAR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2603831,1,14,to_date('05-APR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2759590,1,15,to_date('11-APR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2376294,1,16,to_date('18-APR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2308476,1,17,to_date('26-APR-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2455142,1,18,to_date('02-MAY-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2498546,1,19,to_date('10-MAY-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2457499,1,21,to_date('23-MAY-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2461661.79,1,22,to_date('30-MAY-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2310346,1,23,to_date('06-JUN-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2512139,1,24,to_date('13-JUN-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2267758,1,25,to_date('20-JUN-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2497566,1,26,to_date('27-JUN-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2583208,1,27,to_date('04-JUL-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2195667,1,28,to_date('11-JUL-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2288098,1,29,to_date('18-JUL-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2322778,1,30,to_date('25-JUL-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2306878,1,31,to_date('01-AUG-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2513741,1,32,to_date('08-AUG-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2350728,1,33,to_date('15-AUG-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2554842,1,34,to_date('22-AUG-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2664116,1,35,to_date('29-AUG-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2743931,1,36,to_date('05-SEP-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2501886,1,37,to_date('12-SEP-13','DD-MON-RR'));

    Insert into BALANCE_SHEET (CURR_ACCTS_RECEIVABLE_NET,COMPANY_ID,WEEK_NUMBER,PERIOD) values (2315477,1,38,to_date('19-SEP-13','DD-MON-RR'));

     

    My try so far

     

    SELECT ROUND(temp.CURR_ACCTS_RECEIVABLE_NET / temp.REV) ARdays,

      temp.WEEK_NUMBER,

      temp.CURR_ACCTS_RECEIVABLE_NET,

      temp.REV,

      temp.PERIOD

    FROM

      (SELECT ROUND(SUM(t.rev) / 90) AS REV,

        bs.CURR_ACCTS_RECEIVABLE_NET,

        bs.WEEK_NUMBER,

        bs.PERIOD

      FROM

        (SELECT MO_SETTINGS.REVENUE_PRIOR_MO rev,

          MO_SETTINGS.COMPANY_ID

        FROM MO_SETTINGS

        WHERE MO_SETTINGS.REVENUE_PRIOR_MO IS NOT NULL

        AND MO_SETTINGS.COMPANY_ID          = 1

        AND to_date(MO_SETTINGS.CURR_MO, 'MM') BETWEEN add_months(TRUNC(SysDate, 'MM'), -3) AND TRUNC(SysDate, 'MM')

        AND to_date(MO_SETTINGS.CURR_MO, 'MM') < TRUNC(SysDate, 'MM')

        ORDER BY to_date(CURR_MO, 'MM')

        ) t

      INNER JOIN balance_sheet bs

      ON t.COMPANY_ID = bs.COMPANY_ID

      GROUP BY bs.CURR_ACCTS_RECEIVABLE_NET,

        bs.WEEK_NUMBER,

        bs.PERIOD

      ) temp

    ORDER BY temp.WEEK_NUMBER

    result expected

    group of the 3 months rev. total /90 to get the daily rev value for that period

    for exam Jun Jul Aug 5,385,957 (table 1

    daily revenue value is 59,844 from 01-Jun to 31-aug.

     

    For those 12-13 week (JUN-AUG) table 2 Ar $ value should be divide by  59,844 to get AR days

     

    Ar days week numberar $calculation
    39 3123068782306878/59844
              42 3225137412513741/59844
              39 3323507282350728/59844
              43 3425548422554842/59844
              45 3526641162664116/59844
              46 3627439312743931/59844
              42 3725018862501886/59844
              39 3823154772315477/59844
  • 3. Re: sql calculation help needed.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Thanks for posting the INSERT statements.  Don't forget to post the CREATE TABLE statements and an explanation of how you get the desired results from the given sample data.  For example "The results start with week 31 because ... Ther ar$ value on that row, 2306878 is calculated like this ... and the value 59844 is calculated like this ... Company_id is always 1, and it is included in the tables because ... "

  • 4. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    OK Frank

     

    CREATE TABLE "BALANCE_SHEET"  ("CURR_ACCTS_RECEIVABLE_NET" NUMBER(11,2),"WEEK_NUMBER" NUMBER(4,0) NOT NULL ENABLE,    "PERIOD" DATE NOT NULL ENABLE, "COMPANY_ID"  NUMBER(2,0));

    CREATE TABLE "MO_SETTINGS" ( "CURR_MO"  VARCHAR2(20 BYTE),"COMPANY_ID"  NUMBER(2,0),"REVENUE_PRIOR_MO" NUMBER(20,2)    );

     

    AR Balance at any point (since we input weekly, use the weekly) divided by the “Average Daily Rev”

     

    Where we will use the “Average Daily Rev” equal to the average daily amount of the last 3 months Rev. Again, add the rev for the 3 months and divide by 90

     

    For Example: for 01-Jun to 31-Aug

                    rev          Jun         $3,000,000

                                    Jul           $3,000,000

                                    Aug        $3,000,000

                    Total                      $9,000,000         

                    Daily average     $100,000   ($9,000,000 divided by 90)

     

    A/R on W/E 8-7                 $2,000,000           Days AR   =$2,000,000 divided by $100,000 = 20 days

    A/R on W/E 8-14               $3,000,000           Days AR =$3,000,000 divided by $100,000 = 30 Days


    same way for May,Jun,Jul and so  on..


    I hope I am able convey it rightly.

    Let me know if have any questions

    I thank you for your time.


  • 5. Re: sql calculation help needed.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    I still don't understand why the output only includes weeks 31-38, when the data includes weeks 10-38.  Does it have to do with when the query is run?  If so, are the results what you want when the query is run on Seprtember 27, 2013?  What results would you want if it were run on, say, August 27, 2013, with the same sample data?

     

    I still don't understand how you get the figures in the output; for example, the numbers 2306878 and 59844 on the row with week=31.

     

    I don't see any of the numbers 3,000,000 or 2,000,000 anywhere in the same data.  Are they derived from the same data somehow?

     

    I still don't understand what role, if any company_id plays in this problem.

  • 6. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    I still don't understand why the output only includes weeks 31-38, when the data includes weeks 10-38.  Does it have to do with when the query is run?  If so, are the results wjhat you want when the query is run on Seprtember 27, 2013?  What results would you want if it were run on, say, August 27, 2013, with the same sample data?

     

     

    31-38 data output is sample data output for the period of Jun-Aug

     

     

    Ok If we cant to see the data today to would be from week 10-38 ..(3 months combine make a revenue total for 90 days;for those 3 months daily revenue would be total/90)

    jan -feb -mar

    Feb-mar-april

    march - april- may

    april-may-jun

    June-jul-aug etc.

     

    ARDAYSweek       AR $daily rev
             47 102589570
              43 112376294Daily Rev.JuneJulyAug3-mo Total
              45 122454919      59,844       1,669,651       1,709,323       2,006,983       5,385,957
              50 132767058
              48 142603831Daily Rev.MayJuneJuly3-mo Total
              51 152759590      56,076       1,667,823       1,669,651       1,709,323       5,046,797
              44 162376294
             42 172308476Daily Rev.AprilMayJune3-mo Total
              44 182455142      55,556       1,662,600       1,667,823       1,669,651       5,000,074
              45 192498546
              44 212457499Daily Rev.MarchAprilMay3-mo Total
              44 222461662      55,332       1,649,450       1,662,600       1,667,823       4,979,873
              42 232310346
              45 242512139Daily Rev.Feb.MarchApril3-mo Total
             41 252267758      54,489       1,591,972       1,649,450       1,662,600       4,904,022
             45 262497566
              46 272583208Daily Rev.JanFeb.March3-mo Total
              39 282195667      55,101       1,717,631       1,591,972       1,649,450       4,959,053
              41 292288098
              41 302322778
             39 312306878
              42 322513741
              39 332350728
              43 342554842
              45 352664116
              46 362743931
              42 372501886
              39 382315477

     

     

     

    I have color highlighted for better understanding

    again the formula to use is like this

     

    1.Take a sum for 3 month period for rev.(table1)

    2. Then divide is by 90 to get the daily rev value(table1) get x$ value for that period for ex Jun-jul-aug is 59,844

    3 In table2 for all those AR $ value following in the date range /week in JUN-AUG use the $ value /59,844 .  To get AR days.

     

     

     

    I still don't understand how you get the figures in the output; for example, the numbers 2306878 and 59844 on the row with week=31.

    • the numbers 2306878 and 59844 not the actual part of output.. this was put over there for understanding only.
    • first two columns is the only output needed.AR days and week numbers

     

     

    I don't see any of the numbers 3,000,000 or 2,000,000 anywhere in the same data.  Are they derived from the same data somehow?

    • Yes they are derived number for understanding the concept.
    • we have more than 1 company in the data for testing and understand we are focusing on company=1 (In my query)
  • 7. Re: sql calculation help needed.
    Mike Kutz Expert
    Currently Being Moderated

    You have a problem with your table design.

    You are storing date information as a string.

    As such, you can't do a proper ORDER BY using a simple syntax.

     

    This uses a "moving window" analytical function.

    The "window" is from 3 months ago to 1 month ago.(technically, 3 rows ago to 1 row ago)

    BUT -- you must be able to properly ORDER the data... which requires a DATE format..

     

    with monthly_data as (

              select to_date('JAN','MON') curr_mo, 0 revenue_prior_mo from dual

    union all select to_date('FEB','MON'), 1717631 from dual

    union all select to_date('MAR','MON'),1519720 from dual

    union all select to_date('APR','MON'),1649450 from dual

    union all select to_date('MAY','MON'),1662600 from dual

    union all select to_date('JUN','MON'),1667823 from dual

    union all select to_date('JUL','MON'),1669651 from dual

    union all select to_date('AUG','MON'),1709323 from dual

    union all select to_date('SEP','MON'),2006983 from dual

    )

    select sum( revenue_prior_mo ) over (order by curr_mo rows between 3 preceding and 1 preceding)

      as prior_3_months

      ,revenue_prior_mo as this_month

      ,curr_mo

      ,to_char(curr_mo,'fmMONTH') current_month

    from monthly_data;

  • 8. Re: sql calculation help needed.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    This produces the output you requested given the sample data you posted:

     

    WITH   got_denominator AS

    (

        SELECT  SUM (revenue_prior_mo) / 90  AS denominator

        FROM    mo_settings

        WHERE   curr_mo IN (

                                   SELECT  TO_CHAR ( ADD_MONTHS ( SYSDATE

                                                                , 1 - LEVEL

                                                                )

                                                   , 'MONTH'

                                                   )

                                   FROM    dual

                                   CONNECT BY   LEVEL  <= 3

                           )

    )

    SELECT    ROUND ( b.curr_accts_receivable_net

                    / d.denominator

                    )                               AS ar_days

    ,         b.week_number

    ,         b.curr_accts_receivable_net           AS ar

    ,         TO_CHAR (ROUND (b.curr_accts_receivable_net))

           || '/'

           || TO_CHAR (ROUND (d.denominator))       AS calculation

    FROM        balance_sheet    b

    CROSS JOIN  got_denominator  d

    WHERE     b.period      >= TRUNC ( ADD_MONTHS (SYSDATE, -1)

                                          , 'MONTH'

                                          )

    ORDER BY  b.week_number

    ;

    As long as I don't understand what you want to do, I can't predict how it will work on any other set of data, but it may give you some ideas.

  • 9. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    Thank so much Frank and Mike  for time and help I really appreciated it.

    while working on this I just realized that rules in not clear. I am seeking more clarification.


    Considering the data I have , we want to calculate the Ar days for week 23-35

     

     

    Daily Rev.JuneJulyAug3-mo Total
          59,844      1,669,651      1,709,323      2,006,983      5,385,957
    Daily Rev.MayJuneJuly3-mo Total
          56,076      1,667,823      1,669,651      1,709,323      5,046,797
    Daily Rev.AprilMayJune3-mo Total
          55,556      1,662,600      1,667,823      1,669,651      5,000,074

     

     

     

    Ar $ valueweek
    2310346236-Jun-13
    25121392413-Jun-13
    22677582520-Jun-13
    24975662627-Jun-13
    2583208274-Jul-13
    21956672811-Jul-13
    22880982918-Jul-13
    23227783025-Jul-13
    2306878311-Aug-13
    2513741328-Aug-13
    23507283315-Aug-13
    25548423422-Aug-13
    26641163529-Aug-13

     

     

    Ar days (for week 23)= 2310346 /(59,844 or 56,076 or 55,556 )


    Since June lies in all three sliding window. I want to know the which point of time, value to use Beginning , middle or end to calc. Ar Days for that week.What happens if week number lies into two months?


    I will update you guys when I get the answer and what I tried after that .


    Again thanks for your help. Have a great weekend.

  • 10. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    I got the answer

    "use the latest 3 completed months for each week"

     

     

    as soon sliding window hit JUNE use june (apr-may-june)value to divide

    output (coulm 1 and 2 only)

     

    ARDAYSWEEK#period AR $
              47103/7/2013 2589570
              43113/14/2013 2376294 Daily Rev.JuneJulyAug3-mo Total
              45123/22/2013 2454919       59,844      1,669,651      1,709,323      2,006,983      5,385,957
              50133/31/2013 2767058
              48144/5/2013 2603831 Daily Rev.MayJuneJuly3-mo Total
              51154/11/2013 2759590       56,076      1,667,823      1,669,651      1,709,323      5,046,797
              44164/18/2013 2376294
              42174/26/2013 2308476 Daily Rev.AprilMayJune3-mo Total
              44185/2/2013 2455142       55,556      1,662,600      1,667,823      1,669,651      5,000,074
              45195/10/2013 2498546
              44215/23/2013 2457499 Daily Rev.MarchAprilMay3-mo Total
              44225/30/2013 2461662       55,332      1,649,450      1,662,600      1,667,823      4,979,873
              42236/6/2013 2310346
              45246/13/2013 2512139 Daily Rev.Feb.MarchApril3-mo Total
              41256/20/2013 2267758       54,489      1,591,972      1,649,450      1,662,600      4,904,022
              45266/27/2013 2497566
              46277/4/2013 2583208 Daily Rev.JanFeb.March3-mo Total
              39287/11/2013 2195667       55,101      1,717,631      1,591,972      1,649,450      4,959,053
              41297/18/2013 2288098
              41307/25/2013 2322778
              39318/1/2013 2306878
              42328/8/2013 2513741
              39338/15/2013 2350728
              43348/22/2013 2554842
              45358/29/2013 2664116
              46369/5/2013 2743931
              42379/12/2013 2501886
              39389/19/2013 2315477

     

    Hope this helps..

    sorry for all the trouble.

  • 11. Re: sql calculation help needed.
    Etbin Guru
    Currently Being Moderated

    with

    mo_settings as

    (select 'MARCH    ' curr_mo,1 company_id,1591972 revenue_prior_mo from dual union all

    select 'FEBRUARY ',1,1717631 from dual union all

    select 'JANUARY  ',1,0 from dual union all

    select 'APRIL    ',1,1649450 from dual union all

    select 'JULY     ',1,1669651 from dual union all

    select 'JUNE     ',1,1667823 from dual union all

    select 'AUGUST   ',1,1709323 from dual union all

    select 'SEPTEMBER',1,2006983 from dual union all

    select 'MAY      ',1,1662600 from dual

    ),

    balance_sheet as

    (select 2589570 curr_accts_receivable_net,1 company_id,10 week_number,to_date('07-MAR-13','DD-MON-RR') period from dual union all

    select 2376294,1,11,to_date('14-MAR-13','DD-MON-RR') from dual union all

    select 2454919,1,12,to_date('22-MAR-13','DD-MON-RR') from dual union all

    select 2767058,1,13,to_date('31-MAR-13','DD-MON-RR') from dual union all

    select 2603831,1,14,to_date('05-APR-13','DD-MON-RR') from dual union all

    select 2759590,1,15,to_date('11-APR-13','DD-MON-RR') from dual union all

    select 2376294,1,16,to_date('18-APR-13','DD-MON-RR') from dual union all

    select 2308476,1,17,to_date('26-APR-13','DD-MON-RR') from dual union all

    select 2455142,1,18,to_date('02-MAY-13','DD-MON-RR') from dual union all

    select 2498546,1,19,to_date('10-MAY-13','DD-MON-RR') from dual union all

    select 2457499,1,21,to_date('23-MAY-13','DD-MON-RR') from dual union all

    select 2461661.79,1,22,to_date('30-MAY-13','DD-MON-RR') from dual union all

    select 2310346,1,23,to_date('06-JUN-13','DD-MON-RR') from dual union all

    select 2512139,1,24,to_date('13-JUN-13','DD-MON-RR') from dual union all

    select 2267758,1,25,to_date('20-JUN-13','DD-MON-RR') from dual union all

    select 2497566,1,26,to_date('27-JUN-13','DD-MON-RR') from dual union all

    select 2583208,1,27,to_date('04-JUL-13','DD-MON-RR') from dual union all

    select 2195667,1,28,to_date('11-JUL-13','DD-MON-RR') from dual union all

    select 2288098,1,29,to_date('18-JUL-13','DD-MON-RR') from dual union all

    select 2322778,1,30,to_date('25-JUL-13','DD-MON-RR') from dual union all

    select 2306878,1,31,to_date('01-AUG-13','DD-MON-RR') from dual union all

    select 2513741,1,32,to_date('08-AUG-13','DD-MON-RR') from dual union all

    select 2350728,1,33,to_date('15-AUG-13','DD-MON-RR') from dual union all

    select 2554842,1,34,to_date('22-AUG-13','DD-MON-RR') from dual union all

    select 2664116,1,35,to_date('29-AUG-13','DD-MON-RR') from dual union all

    select 2743931,1,36,to_date('05-SEP-13','DD-MON-RR') from dual union all

    select 2501886,1,37,to_date('12-SEP-13','DD-MON-RR') from dual union all

    select 2315477,1,38,to_date('19-SEP-13','DD-MON-RR') from dual

    ),

    daily_revenues as

    (select curr_mo, -- revenue_prior_mo,

            case when revenue_prior_mo is not null

                 then round(sum(revenue_prior_mo) over (partition by company_id

                                                            order by to_date(curr_mo,'MONTH')

                                                        rows between 2 preceding and current row

                                                       ) / 90

                           )

                else round(sum(revenue_prior_mo) over (partition by company_id

                                                            order by to_date(curr_mo,'MONTH')

                                                        rows between 3 preceding and 1 preceding

                                                       ) / 90

                           )

            end daily_revenue

       from (select company_id,

                    curr_mo,

                    lead(revenue_prior_mo) over (partition by company_id

                                                     order by to_date(curr_mo,'MONTH')

                                                ) revenue_prior_mo

               from mo_settings

            )

    )

    select round(b.curr_accts_receivable_net / r.daily_revenue) ar_days,

           b.week_number

      from balance_sheet b,

           daily_revenues r

    where to_char(b.period,'MONTH') = r.curr_mo

    order by b.week_number

     

    AR_DAYSWEEK_NUMBER
    4710
    4311
    4512
    5013
    4814
    5115
    4416
    4217
    4418
    4519
    4421
    4422
    4223
    4524
    4125
    4526
    4627
    3928
    4129
    4130
    3931
    4232
    3933
    4334
    4535
    4636
    4237
    3938

     

    Regards

     

    Etbin

  • 12. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    Thanks Etbin for helping me on this . I believe the approach over here is creating pseudo table in query. But the data in entered every week in balance sheet table and in monthly setting table once a month. Defining it static wont work in long run :(

  • 13. Re: sql calculation help needed.
    Etbin Guru
    Currently Being Moderated
    I believe the approach over here is creating pseudo table in query.

    Yes, your tables are simulated.

    I don't want to create tables as my Apex tablespace is quite crowded already.

    I find much more practical keeping data and code together.

    The query could look something like the one below and should work on your tables (provided the table and column names are correct)

     

    select round(b.curr_accts_receivable_net / r.daily_revenue) ar_days,

           b.week_number

      from balance_sheet b,

           (select curr_mo,

                   case when revenue_prior_mo is not null

                       then round(sum(revenue_prior_mo) over (partition by company_id

                                                                   order by to_date(curr_mo,'MONTH')

                                                               rows between 2 preceding and current row

                                                             ) / 90

                                 )

                       else round(sum(revenue_prior_mo) over (partition by company_id

                                                                   order by to_date(curr_mo,'MONTH')

                                                               rows between 3 preceding and 1 preceding

                                                             ) / 90

                                 )

                   end daily_revenue

              from (select company_id,

                           curr_mo,

                           lead(revenue_prior_mo) over (partition by company_id

                                                           order by to_date(curr_mo,'MONTH')

                                                       ) revenue_prior_mo

                     from mo_settings

                   )

           ) r

    where to_char(b.period,'MONTH') = r.curr_mo

    order by b.week_number

     

    Regards

     

    Etbin

  • 14. Re: sql calculation help needed.
    n_shah18 Newbie
    Currently Being Moderated

    Thanks so much Etbin. Your help and time is highly appreciated. Mike and Frank I appreciated your idea sharing with me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points