David Bicking <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
