Query for earliest due date

[Ramesh]

I have table A with promises of payment, Amtpayable , Duedate and CustID.
I have table B with actual payment details, AmtPaid, PaidDate, Cust ID.

Now i am creating a query to show the total AmtPayable as on date, amount
paid, and LastdueDate. Now i have no problem getting the payable and paid
columns. Problem is i get only the Lastduedate till date but i need
earliest unpaid due date.

for eg, due for jan 1 is 1000, for feb 1 is 1000, mar 1 is 1000, etc. if
only 1000 has been paid so far, the query as on date should show Payable
3000, Paid 1000, Due date Feb1. But i get duedate as Mar 1.

Any help on how i can correct this?

Thanks very much
Ramesh

 

[Ken Sheridan]

Ramesh:

Try the following. It shuld take account of partial payents as well as full
payments on the due date, e.g if in your example only 500 was paid on 1 Feb
rather than the 1,000 due then the next due date would still be Feb 1 as 500
is still outstanding at that date.

SELECT CustID,
SUM(AmtPayable) AS TotalPayable,
NZ((SELECT SUM(AmtPaid)
FROM B as B1
WHERE B1.CustID = A1.CustID),0) AS Paid,
SUM(AmtPayable) -
NZ((SELECT SUM(AmtPaid)
FROM B as B2
WHERE B2.CustID = A1.CustID),0) AS BalanceDue,
(SELECT MIN(DueDate)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND (SELECT SUM(AmtPayable)
FROM A AS A3
WHERE A3.CustID = A1.CustID
AND A3.DueDate <= A2.DueDate) >
NZ((SELECT SUM(AmtPaid)
FROM B AS B3
WHERE B3.CustID = A1.CustID),0)) AS NextDueDate
FROM A AS A1
GROUP BY CustID;

Ken Sheridan
Stafford, England

 

[Ramesh]

Thasnk a lot Ken.

In fact after i got this, i thought i use this date and list all the breakup
of dues and dates from that date. that would be more meaningful forthe
customer. hope am able to do that. Maybe u could give me a clue. Would it
as simple as just removing the Sum function?

Thanks again
Ramesh

 

[Ken Sheridan]

Ramesh:

The following should work, and take account of partial payments, provided
that all payments are made on the actual due dates. It would not take
account of late partial payments, so if 600 was paid on 2 Feb then it would
show the full 1000 due on 1 Feb, not the balance of 400. It would take
account of late full payments, however, so if 1000 was paid on 2 Feb it would
show the first due payment as 1000 on 1 March:

SELECT CustID, DueDate,
((SELECT SUM(AmtPayable)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND A2.DueDate <= A1.DueDate)-
(SELECT SUM(AmtPaid)
FROM B AS B2
WHERE B2.CustID = A1.CustID
AND B2.PaidDate <= A1.DueDate))-
((SELECT SUM(AmtPayable)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND A2.DueDate < A1.DueDate)-
(SELECT SUM(AmtPaid)
FROM B AS B2
WHERE B2.CustID = A1.CustID
AND B2.PaidDate < A1.DueDate))
AS AmountDue
FROM A AS A1
WHERE (SELECT SUM(AmtPaid)
FROM B AS B1
WHERE CustID = A1.CustID) <
(SELECT SUM(AmtPayable)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND A2.DueDate <= A1.DueDate)
ORDER BY CustID, DueDate;

Ken Sheridan
Stafford, England

 

[Ramesh]

Thanks Ken but that may not really work in my scenario cos payments come in
different amounts and dates. The amount may be less or more than the due
amount and payment rarely happens on the due date.

is there another way i could do this?

hope i am not being too greedy

Thanks for all your effort.

Ramesh

 

[Ken Sheridan]

The nearest I can get is to return the total outstanding balances at any due
date:

SELECT CustID, Duedate,
(SELECT SUM(AmtBable)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND A2.Duedate <= A1.Duedate) -
(SELECT SUM(AmtPaid)
FROM B AS B1
WHERE B1.CustID = A1.CustID
AND B1.PaidDate <= A1.Duedate)
AS TotalOutstandingBalance
FROM A AS A1
WHERE DueDate =
(SELECT MIN(Duedate)
FROM A AS A2
WHERE A2.CustID = A1.CustID
AND A2.DueDate >= A1.DueDate)
ORDER BY CustID, DueDate;

If we take the following tables:

A
CustID Duedate AmtPayable
1 01/01/2007 £1,000.00
1 01/02/2007 £1,000.00
1 01/03/2007 £1,000.00
1 01/04/2007 £1,000.00
2 01/02/2007 £500.00
2 01/03/2007 £500.00
2 01/04/2007 £500.00

and

B
CustID PaidDate AmtPaid
1 01/01/2007 £1,000.00
1 02/02/2007 £500.00
1 10/02/2007 £100.00
2 01/02/2007 £500.00

The result set of the query would be:

CustID Duedate TotalOutstandingBalance
1 01/01/2007 £0.00
1 01/02/2007 £1,000.00
1 01/03/2007 £1,400.00
1 01/04/2007 £2,400.00
2 01/02/2007 £0.00
2 01/03/2007 £500.00
2 01/04/2007 £1,000.00

For CustID 1 you'll see that the total outstanding balance at 1 Feb (the
dates above are in UK dd/mm/yyyy format) is 1,000 GBP as the payment due on
that date had not then been made. With the subsequent two payments on 2 Feb
and 10 Feb totalling 600 GBP, however, the total outstanding balance at 1
March is 1,400 GBP, i.e. the 1,000 GBP due on that date plus the 400 GBP
still outstanding from the earlier due payment. The total outstanding
balance at 1 April is 2,400 GBP, i.e. the balance which would be due at that
date if no further payments were made following those currently recorded in
table B.

I think that's the best I can manage. I may well have lost sight of the
wood for the trees, however, and there is a simple solution staring me in the
face! I'll sleep on it and if anything springs to mind I'll get back to you.

Ken Sheridan
Stafford, England