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

# sql calculation help needed.

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

 2.58957e+06 10 07-MAR-13 2.37629e+06 11 14-MAR-13 2.45492e+06 12 22-MAR-13 2.76706e+06 13 31-MAR-13 2.60383e+06 14 05-APR-13 2.75959e+06 15 11-APR-13 2.37629e+06 16 18-APR-13 2.30848e+06 17 26-APR-13 2.45514e+06 18 02-MAY-13 2.49855e+06 19 10-MAY-13 2.4575e+06 21 23-MAY-13 2.46166e+06 22 30-MAY-13 2.31035e+06 23 06-JUN-13 2.51214e+06 24 13-JUN-13 2.26776e+06 25 20-JUN-13 2.49757e+06 26 27-JUN-13 2.58321e+06 27 04-JUL-13 2.19567e+06 28 11-JUL-13 2.2881e+06 29 18-JUL-13 2.32278e+06 30 25-JUL-13 2.30688e+06 31 01-AUG-13 2.51374e+06 32 08-AUG-13 2.35073e+06 33 15-AUG-13 2.55484e+06 34 22-AUG-13 2.66412e+06 35 29-AUG-13 2.74393e+06 36 05-SEP-13 2.50189e+06 37 12-SEP-13 2.31548e+06 38 19-SEP-13

I want to take sum of 3 months corresponding

 Daily Rev. June July Aug 3-mo Total 59,844 1,669,651 1,709,323 2,006,983 5,385,957 Daily Rev. May June July 3-mo Total 56,076 1,667,823 1,669,651 1,709,323 5,046,797 Daily Rev. April May June 3-mo Total 55,556 1,662,600 1,667,823 1,669,651 5,000,074 Daily Rev. March April May 3-mo Total 55,332 1,649,450 1,662,600 1,667,823 4,979,873 Daily Rev. Feb. March April 3-mo Total 54,489 1,591,972 1,649,450 1,662,600 4,904,022 Daily Rev. Jan Feb. March 3-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)

 ARDAYS WEEK_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.
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.
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 number ar \$ calculation 39 31 2306878 2306878/59844 42 32 2513741 2513741/59844 39 33 2350728 2350728/59844 43 34 2554842 2554842/59844 45 35 2664116 2664116/59844 46 36 2743931 2743931/59844 42 37 2501886 2501886/59844 39 38 2315477 2315477/59844
• ###### 3. Re: sql calculation help needed.
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.
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.
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.
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.

 ARDAYS week       AR \$ daily rev 47 10 2589570 43 11 2376294 Daily Rev. June July Aug 3-mo Total 45 12 2454919 59,844 1,669,651 1,709,323 2,006,983 5,385,957 50 13 2767058 48 14 2603831 Daily Rev. May June July 3-mo Total 51 15 2759590 56,076 1,667,823 1,669,651 1,709,323 5,046,797 44 16 2376294 42 17 2308476 Daily Rev. April May June 3-mo Total 44 18 2455142 55,556 1,662,600 1,667,823 1,669,651 5,000,074 45 19 2498546 44 21 2457499 Daily Rev. March April May 3-mo Total 44 22 2461662 55,332 1,649,450 1,662,600 1,667,823 4,979,873 42 23 2310346 45 24 2512139 Daily Rev. Feb. March April 3-mo Total 41 25 2267758 54,489 1,591,972 1,649,450 1,662,600 4,904,022 45 26 2497566 46 27 2583208 Daily Rev. Jan Feb. March 3-mo Total 39 28 2195667 55,101 1,717,631 1,591,972 1,649,450 4,959,053 41 29 2288098 41 30 2322778 39 31 2306878 42 32 2513741 39 33 2350728 43 34 2554842 45 35 2664116 46 36 2743931 42 37 2501886 39 38 2315477

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.
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.
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.
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. June July Aug 3-mo Total 59,844 1,669,651 1,709,323 2,006,983 5,385,957 Daily Rev. May June July 3-mo Total 56,076 1,667,823 1,669,651 1,709,323 5,046,797 Daily Rev. April May June 3-mo Total 55,556 1,662,600 1,667,823 1,669,651 5,000,074

 Ar \$ value week 2310346 23 6-Jun-13 2512139 24 13-Jun-13 2267758 25 20-Jun-13 2497566 26 27-Jun-13 2583208 27 4-Jul-13 2195667 28 11-Jul-13 2288098 29 18-Jul-13 2322778 30 25-Jul-13 2306878 31 1-Aug-13 2513741 32 8-Aug-13 2350728 33 15-Aug-13 2554842 34 22-Aug-13 2664116 35 29-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.
Currently Being Moderated

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

 ARDAYS WEEK# period AR \$ 47 10 3/7/2013 2589570 43 11 3/14/2013 2376294 Daily Rev. June July Aug 3-mo Total 45 12 3/22/2013 2454919 59,844 1,669,651 1,709,323 2,006,983 5,385,957 50 13 3/31/2013 2767058 48 14 4/5/2013 2603831 Daily Rev. May June July 3-mo Total 51 15 4/11/2013 2759590 56,076 1,667,823 1,669,651 1,709,323 5,046,797 44 16 4/18/2013 2376294 42 17 4/26/2013 2308476 Daily Rev. April May June 3-mo Total 44 18 5/2/2013 2455142 55,556 1,662,600 1,667,823 1,669,651 5,000,074 45 19 5/10/2013 2498546 44 21 5/23/2013 2457499 Daily Rev. March April May 3-mo Total 44 22 5/30/2013 2461662 55,332 1,649,450 1,662,600 1,667,823 4,979,873 42 23 6/6/2013 2310346 45 24 6/13/2013 2512139 Daily Rev. Feb. March April 3-mo Total 41 25 6/20/2013 2267758 54,489 1,591,972 1,649,450 1,662,600 4,904,022 45 26 6/27/2013 2497566 46 27 7/4/2013 2583208 Daily Rev. Jan Feb. March 3-mo Total 39 28 7/11/2013 2195667 55,101 1,717,631 1,591,972 1,649,450 4,959,053 41 29 7/18/2013 2288098 41 30 7/25/2013 2322778 39 31 8/1/2013 2306878 42 32 8/8/2013 2513741 39 33 8/15/2013 2350728 43 34 8/22/2013 2554842 45 35 8/29/2013 2664116 46 36 9/5/2013 2743931 42 37 9/12/2013 2501886 39 38 9/19/2013 2315477

Hope this helps..

sorry for all the trouble.

• ###### 11. Re: sql calculation help needed.
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,

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.
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.
Currently Being Moderated
I believe the approach over here is creating pseudo table in query.

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,

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