14 Replies Latest reply: Sep 30, 2013 2:09 PM by n_shah18 RSS

    sql calculation help needed.

    n_shah18

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

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

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