CODEWITHSUNDEEP
mysqlselect
manta
manta

Reputation: 11

Compute outstanding amounts in MySQL

I am having an issue with a SELECT command in MySQL. I have a database of securities exchanged daily with maturity from 1 to 1000 days (>1 mio rows). I would like to get the outstanding amount per day (and possibly per category). To give an example, suppose this is my initial dataset:

DATE    VALUE    MATURITY
1       10       3       
1       15       2
2       10       1
3       5        1

I would like to get the following output

DATE    OUTSTANDING_AMOUNT
1       25
2       35
3       15

Outstanding amount is calculated as the total of securities exchanged still 'alive'. That means, in day 2 there is a new exchange for 10 and two old exchanges (10 and 15) still outstanding as their maturity is longer than one day, for a total outstanding amount of 35 on day 2. In day 3 instead there is a new exchange for 5 and an old exchange from day 1 of 10. That is, 15 of outstanding amount.

Here's a more visual explanation:

Monday      Tuesday     Wednesday
10          10          10             (Day 1, Value 10, matures in 3 days)
15          15                         (Day 1, 15, 2 days)
            10                         (Day 2, 10, 1 day)
                        5              (Day 3, 5, 3 days with remainder not shown)
-------------------------------------
25          35          15             (Outstanding amount on each day)

Is there a simple way to get this result?

Upvotes: 1

Views: 167

Answers (4)

peterm
peterm

Reputation: 92785

A possible solution with a tally (numbers) table

SELECT date, SUM(value) outstanding_amount
  FROM
(
  SELECT date + maturity - n.n date, value, maturity
    FROM table1 t JOIN
  (
    SELECT 1 n UNION ALL
    SELECT 2 UNION ALL 
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 
  ) n ON n.n <= maturity
) q
 GROUP BY date

Output:

| DATE | OUTSTANDING_AMOUNT |
-----------------------------
|    1 |                 25 |
|    2 |                 35 |
|    3 |                 15 |

Here is SQLFiddle demo

Upvotes: 0

valex
valex

Reputation: 24144

First of all in the main subquery we find SUM of all Values for current date. Then add to them values from previous dates according their MATURITY (the second subquery).

SQLFiddle demo

select T1.Date,T1.SumValue+
IFNULL((select SUM(VALUE) 
          from T 
          where 
             T1.Date between 
                T.Date+1 and T.Date+Maturity-1 )
       ,0)
FROM
(
   select Date,
       sum(Value) as SumValue
   from T
   group by Date
) T1
order by DATE

Upvotes: 1

Caleth
Caleth

Reputation: 62684

Each date considers each row for inclusion in the summation of value

SELECT d.DATE, SUM(m.VALUE) AS OUTSTANDING_AMOUNT
FROM yourTable AS d JOIN yourtable AS m ON d.DATE >= m.MATURITY
GROUP BY d.DATE
ORDER BY d.DATE

Upvotes: 0

Amd4632
Amd4632

Reputation: 199

I'm not sure if this is what you are looking for, perhaps if you give more detail

select
   DATE
   ,sum(VALUE) as OUTSTANDING_AMOUNT
from
  NameOfYourTable

group by 
   DATE
Order by
   DATE

I hope this helps

Upvotes: 0

Related Questions