correlated sql sub query problems 2

[ecannelora]

I have two simple tables:

tblSched
-sched date
-sched amt

tblPayments(received)
-pmt date
-pmt amt

let's say my data looks like this (this would be for one customer, by the way)

tblSched tblPayments

1/1/05 100 1/1/05 150
2/1/05 100 2/1/05 150
3/1/05 100
4/1/05 100

one could look at this and see the next scheduled payment would not be due until 4/1, because the amount received has exceeded the amount scheduled. The amount received is 300, and in the Schedule table, 300 gets you through 3/1/05, so there's no scheduled payment due until 4/1/05

Here's my starting point for the query:

SELECT Max([PmtReceivedDate]) AS ["last pay date"], Sum([PmtAmtReceived]) AS ["amt paidl"], (SELECT Min([SchedDate]) 
  FROM tblSched  
  WHERE [SchedDate]>= [PmtReceivedDate] and [SchedDate]>=Date() and [Customer]='xxxx') AS ["due date"]
FROM tblPayments
WHERE [customer]='xxxx';

this query doesn't really do the trick. It just gives me the next scheduled date that is greater than the system date and greater than the last payment date. I need it to return the next scheduled date where the amount received is greater than the scheduled amount. Can anybody help?

 

[PHV]

You may try something like this (typed, untested):
SELECT S.Customer, Min(S.SchedDate) AS DueDate
FROM (
SELECT A.Customer, A.SchedDate, Sum(B.SchedAmt) AS AmtSched
FROM tblSched AS A INNER JOIN tblSched AS B ON A.Customer = B.Customer AND A.SchedDate >= B.SchedDate
GROUP BY A.Customer, A.SchedDate
) AS S LEFT JOIN (
SELECT Customer, Max(PmtReceivedDate) AS LastPayDate, Sum(PmtAmtReceived) AS AmtPaid
FROM tblPayments GROUP BY CUSTOMER
) AS P ON S.Customer = P.Customer
WHERE S.SchedDate >= Date() AND S.AmtSched > Nz(P.AmtPaid,0)
GROUP BY S.Customer;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ecannelora]

I'm unfamiliar with the dot operator. Can you briefly summarize what S.Customer does, or maybe point me to a SQL reference that could explain it?

 

[lespaul]

S is an alais for this clause:

SELECT A.Customer, A.SchedDate, Sum(B.SchedAmt) AS AmtSched
FROM tblSched AS A INNER JOIN tblSched AS B ON A.Customer = B.Customer AND A.SchedDate >= B.SchedDate
GROUP BY A.Customer, A.SchedDate
) [b]AS S[/b]

PHV is getting the customerid, the scheduled date and the sum of the scheduled amount and joining it on the customer and where 1 scheduled date is greater than the other scheduled date.




Leslie

 

[PHV]

S, A, B and P are alias for table names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ecannelora]

oh yes, you guys are quick. I'm trying to use the query you showed me and I think it will work. I'll keep you posted, and thanks a lot! this is really great

 

[ecannelora]

PHV, that query works brilliantly. I think it will take me an hour to figure out how it works, but I really appreciate your help with it.

lespaul, I also appreciate your help.