Re: [sqlite] Calculating MSO

[Igor Tandetnik]

David Bicking  wrote:
> I need to calculate Months Sales Outstanding.
> 
> CREATE TABLE AR
> Cust Text
> AR Double
> 
> CREATE TABLE Sales
> Cust Text
> Per Integer -- runs 1, 2, 3, etc
> Sales Double
> 
> 
> The calculation is that for each customer:
> MSO = (Per + (AR-sum(Sales)/Sales))
> Where Per and Sales are for the lowest
> Period where Sum(Sales) is greater than the AR

Something like this:

select Cust, (Per + (AR - SumSales) / Sales) MSO from (
  select s.Cust Cust, AR, Per, Sales,
  (select sum(s2.Sales) from Sales s2
   where s2.Cust = s.Cust and s2.Per <= s.Per) SumSales
  from AR join Sales s on (AR.Cust = s.Cust)
  where SumSales > AR and SumSales - s.Sales <= AR
);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Calculating MSO

[David Bicking]

Sorry I messed up the parens. the actual formula is more complicated, so I 
decided to hastily take the outer most layers away messing up the formula in 
the process.
MSO = (Per + (AR-sum(Sales))/Sales)

And yes, result should be 2.7 not 2.3. Stupid typo on my part. 


Thankfully I just got out of a meeting where I was told "it can't be done 
efficiently" is an acceptable answer at this time.I just need to make sure I'm 
not missing some obvious way to get the answer out of the data.
David




 From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org 
Sent: Friday, December 16, 2011 4:05 PM
Subject: Re: [sqlite] Calculating MSO
 
David Bicking <dbic...@yahoo.com> wrote:
> The calculation is that for each customer:
> MSO = (Per + (AR-sum(Sales)/Sales))
> 
> Result
> Cust MSO
> 01 2.3 = (3+(100-120)/60))

You have more closing parentheses here than opening ones. If we ignore the 
rightmot closing paren, the grouping of actual numbers doesn't match the 
grouping of variables in the formula. What is being divided by Sales: 
sum(Sales) or (AR - sum(Sales)) ?

Further, (3+(100-120)/60) == 2.7, not 2.3. Your example doesn't add up.

> Hopefully I described that in a meaningful way. Is it possible to do that 
> efficiently or at all using SQL?

I'd bet it is possible (I might take a stab at it once you clarify the problem 
statement), but I doubt it'd be efficient.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Calculating MSO

[Igor Tandetnik]

David Bicking  wrote:
> The calculation is that for each customer:
> MSO = (Per + (AR-sum(Sales)/Sales))
> 
> Result
> Cust MSO
> 01 2.3 = (3+(100-120)/60))

You have more closing parentheses here than opening ones. If we ignore the 
rightmot closing paren, the grouping of actual numbers doesn't match the 
grouping of variables in the formula. What is being divided by Sales: 
sum(Sales) or (AR - sum(Sales)) ?

Further, (3+(100-120)/60) == 2.7, not 2.3. Your example doesn't add up.

> Hopefully I described that in a meaningful way. Is it possible to do that 
> efficiently or at all using SQL?

I'd bet it is possible (I might take a stab at it once you clarify the problem 
statement), but I doubt it'd be efficient.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Calculating MSO

[David Bicking]

I need to calculate Months Sales Outstanding.

CREATE TABLE  AR
    Cust    Text
    AR    Double

CREATE TABLE Sales
    Cust    Text
    Per    Integer -- runs 1, 2, 3, etc
    Sales    Double 


The calculation is that for each customer: 
MSO = (Per + (AR-sum(Sales)/Sales))    
    Where Per and Sales are for the lowest 
    Period where Sum(Sales) is greater than the AR 

So 

AR
Cust        AR
01    100
02     50

Sales
Cust        Per    Sales
01    1    60
01    3    60
01    4    60
02    1    15
02    2    15
02    3    15
02    4    15

Result
Cust        MSO
01    2.3   = (3+(100-120)/60))
02    3.3   = (4+(50-60)/15))

    Hopefully I described that in a meaningful way. Is it possible to do that 
efficiently or at all using SQL?

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users