How to write SQL to select the not covered InvoiceDueDate

Question

I Have these tables. I need to select the InvoiceDueDate in overDue, the logic is:

PendingCredit = Amount alread paid
Select which InvoiceDueDate doesn't cover the amount already paid.

Something like:

var table1 = select * 
             from Table1 
             order by InvoiceDueDate 
             where Balance > 0 
             and ReceivableId = 72
var pending = 940.13

foreach(var record in table1)
{
   pending = pending - balance
   if(pending <= 0)
      return InvoiceDueDate
}

Table 1

╔══════════════╦═══════════════╦═════════╦════════════════╗
║ ReceivableId ║ PendingCredit ║ Balance ║ InvoiceDueDate ║
╠══════════════╬═══════════════╬═════════╬════════════════╣
║           72 ║ 940.13        ║ 183.79  ║ 21/10/2014     ║
║           72 ║ 940.13        ║ 90.87   ║ 27/10/2014     ║
║           72 ║ 940.13        ║ 160.55  ║ 28/10/2014     ║
║           72 ║ 940.13        ║ 92.03   ║ 3/11/2014      ║
║           72 ║ 940.13        ║ 200.9   ║ 4/11/2014      ║
║           72 ║ 940.13        ║ 15.02   ║ 6/11/2014      ║
║           72 ║ 940.13        ║ 34.96   ║ 6/11/2014      ║
║           72 ║ 940.13        ║ 108.15  ║ 10/11/2014     ║
║           72 ║ 940.13        ║ 27.48   ║ 17/11/2014     ║
║           72 ║ 940.13        ║ 26.38   ║ 1/12/2014      ║
╚══════════════╩═══════════════╩═════════╩════════════════╝

This may be of interest to you: http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance — Jacob, Jul 29 '15 at 02:17

score 3 · Accepted Answer · answered Jul 29 '15 at 02:19

3

You simply need to calculate the cumulative sum and subtract that from the credit:

select invoiceduedate
from (select t1.*,
             sum(balance) over (partition by receivableid order by invoiceduedate) as cumebalance
      from table1 t1
     ) t1
where (cumebalance - balance) <= PendingCredit and
      cumebalance > PendingCredit;

The where clause is a bit complicated. If you have only one receivable, then you can do:

select top 1 invoiceduedate
from (select t1.*,
             sum(balance) over (partition by receivableid order by invoiceduedate) as cumebalance
      from table1 t1
     ) t1
where cumebalance > PendingCredit
order by invoiceduedate;

answered Jul 29 '15 at 02:19

Gordon Linoff

1,242,037
58
646
786

If it helps the PendingCredit is a value comming from another table I put it together in the Table1, but it can be set by parameter if necessary... – Roger Oliveira Jul 29 '15 at 02:29
Also, I am using a Stored Procedure, if it helps to create something more complex, I can also use temporary table if necessary.. – Roger Oliveira Jul 29 '15 at 02:32

How to write SQL to select the not covered InvoiceDueDate

1 Answers1