This will get me what I need based on an entered client id, but what is I want it to return the last payment date and amt for all loans? I tried removing the two where clauses and it only returned the last payment entered but not for all loans.
SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE (dbo.tblLoan.Client_ID = @.Client_ID)AND dbo.tblPayments.PaymentDate = (SELECT TOP 1 p.PaymentDateFROM dbo.tblPayments pINNERJOIN dbo.tblLoan lON l.Loan_ID = p.Loan_IDWHERE l.Client_ID = @.Client_IDORDER BY p.PaymentDateDESC)GROUP BY dbo.tblLoan.Client_ID
Can you give the exact design of the two tables, which fileds are what ...i mean primary key etc?
|||Considering the Loan_ID will be unique in tblLoan
SELECT l.Client_ID,MAX(p.PaymentDate)AS'Last Pay Date',SUM (p.AmountPaid)AS'Total Amount Paid'FROM tblPayments pINNERJOIN tblLoan lON p.Loan_ID = l.Loan_IDWHERE l.Client_ID = 1GROUP BY l.Client_ID|||
Addie,
I tried your suggestion but I need to see the last pay date and amt for all clients. This only shows if there is a clientid of 1.
I also tried removing the Where clause, that gave me the last pay date but it gave me a total sum of payments and I need just the amount of the last payment and date.
Then I remved the Sum but that made the query return all payments for all clients.
How can I make it show each client and there last pay date and last pay amt?
|||Post some sample data from each of the tables in question and the expected output. Sometimes it is easier to see what you are trying to do just by looking at the data than your explanation.
|||ndinakar,
I'm not able to provide data, however, below is an example of what I need:
ClientID Last Pay Date Last Pay Amt10010001 2/2/2007 $10010002001 3/2/2007 $200Eachof the clients may have made several paymentsover time but I needto seeonly these three fieldsfor each client,for their LAST payment made.|||
Try this:
I have added Loan_ID to the results row; it seems possible one client might have two loans?
If you are absolutely sure that cannot happen, then simply remove Loan_ID from the first and last lines.
If you happen to have two or more payments for the same loan on the same day, you'll get the total.
SELECT dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_ID, max(dbo.tblPayments.PaymentDate)AS [Last Pay Date], sum(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)
Group by dbo.tblLoan.Client_ID, dbo.tblLoan.LoanID
|||
I think we are making progress, but that is not it yet. I'm going to make it easier for us.
Clients make payments on loans, they are inserted into tblPayments. I need to return a list of the LAST PAYMENT made by each client. I need to see clientID, PaymentDate, and PaymentAmount.
client1 2/2/2007 $100 < this is the last payment they made, they may have made other payments but this was the last.
client2 3/2/2007 $50 < this is the last payment they made, they may have made other payments but this was the last.
client3 4/1/2007 $1000 < thi si the last payment they made, they may have made other payments but this was the last.
etc...
|||Without providing any table structure or sample data to work on your query it will only be a guessing game. Its just a matter of luck as to who takes the best guess. From your initial query it looks like the two tables are related by LoanId. But what is the relation between LoanId and ClientId?
|||
Did you look at my last post? It shows a simplified version of what I need. I have taken the loan table out of the query, it is not needed.
I cannot beleive that this is so hard to do in SLQ Server. I just want a list of the last payment made by each client that is in tblPayments.
tblPayments
ClientID, PaymentDate, PaymentAmount
|||Yes I did look at your last post. Stating what you want can help if you also provide the source data from which you intend to achieve the result. As I mentioned after making the assumptions based on the info you provided here's a sample:
Declare @.paymentstable (PaymentIdint, Paymentdatedatetime , amountdecimal(10,2), ClientIdint)Insert into @.paymentsSelect 1,'01/01/2007', 100.00,10010001unionallSelect 2,'02/01/2007', 125.00,10010001unionallSelect 10,'02/01/2007', 125.00,10002001unionallSelect 11,'03/01/2007', 125.00,10002001unionallSelect 12,'04/01/2007', 255.00,10002001select P.*from @.payments PJoin (select Clientid,Max(PaymentDate)as MaxDatefrom @.paymentsGroup by Clientid) P2ON P.ClientId = P2.clientidAND P.Paymentdate = P2.maxdate
And, what you are trying to do is very simple, if you provided all the required info.
|||I think I may have worded my post in a way that you did not like, for that I apologize. That is never my intention.|||
SELECT dbo.tblLoan.Client_ID, dbo.tblPayments.PaymentDateAS [Last Pay Date], dbo.tblPayments.AmountPaidAS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)
order by dbo.tblLoan.Client_ID
|||
I mis understood the question, below is what you are perhaps looking for:
SELECT dbo.tblLoan.Client_ID,dbo.tblLoan.Loan_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT MAX( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)GROUP BY dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_IDORDER BY Client_ID
No comments:
Post a Comment