Solved

Sub Select Query to get Last Payment and Date

Posted on 2007-12-03
9
804 Views
Last Modified: 2010-04-21
Hello EE,

I have a query that gets a list of clients who have a small balance.  I also need to include the last payment amount and last payment date in the query.  The following query is what I have so far, but it takes way to long to execute.

select distinct(pd.clientid), pd.clientfirstname, pd.clientlastname, c.creditorid, c.creditorname, dw.id, dw.accountnumber, dw.balanceCurrent, dw.balanceInitial,
(SELECT TOP 1 (r.paymentAmount) from receipts, personalData where receipts.clientid = personalData.clientid ORDER BY receiptid desc) as LastReceipt,
(SELECT TOP 1 (r.datePaymentMade) from receipts, personalData where receipts.clientid = personalData.clientid ORDER BY receiptid desc) as LastPaymentDate
from personalData pd, debtWorksheet dw, creditors c, dmpStatus dmp, disbursements d, receipts r
where pd.clientid = dw.clientid and dw.creditorid = c.creditorid and pd.clientid = dmp.clientid and pd.clientid = d.clientid
and pd.clientid = r.clientid
and (pd.activeStatus IS NOT NULL)
order by pd.clientlastname asc

This query takes a ton of time to execute.  The sub selects are causing the slow down and I need to basically generate the list and include the last payment amount and last payment date from the receipts table.

Any help is greatly appreciated.

Craig
0
Comment
Question by:CraigDerington
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:pai_prasad
ID: 20399481
check if instead of

(SELECT TOP 1 (r.paymentAmount) from receipts, personalData where receipts.clientid = personalData.clientid ORDER BY receiptid desc) as LastReceipt,

this helps

Select max(r.paymentAmount)
from receipts, personalData
where receipts.clientid = personalData.clientid
like wise for the other one..

post table schema , sample data and expected results for solve the problem
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20399627
Hi,

I've changed the joins to ANSI join syntax, and added another table to the join, where we derived the last payment date and the last payment amount.

HTH
  David
select 
	distinct( pd.clientid )
	, pd.clientfirstname
	, pd.clientlastname
	, c.creditorid
	, c.creditorname
	, dw.id
	, dw.accountnumber
	, dw.balanceCurrent
	, dw.balanceInitial
/*	, (
		SELECT TOP 1 (r.paymentAmount)
		from receipts, personalData
		where receipts.clientid = personalData.clientid
		ORDER BY receiptid desc
	) as LastReceipt
*/
	, lr.paymentAmount
/*
	, (
		SELECT TOP 1 (r.datePaymentMade)
		from receipts, personalData
		where receipts.clientid = personalData.clientid
		ORDER BY receiptid desc
	) as LastPaymentDate
*/
	, lr.datePaymentMade
/*
from personalData pd, debtWorksheet dw, creditors c, dmpStatus dmp, disbursements d, receipts r
where
	-- debtWorksheet
	pd.clientid = dw.clientid
	-- creditor
	and dw.creditorid = c.creditorid
	-- dmpStatus
	and pd.clientid = dmp.clientid
	-- disbursements
	and pd.clientid = d.clientid
	-- receipts
	and pd.clientid = r.clientid
	-- where condition
	and (pd.activeStatus IS NOT NULL)
*/
from personalData pd
inner join debtWorksheet dw
	on pd.clientid = dw.clientid
inner join creditors c
	on dw.creditorid = c.creditorid
inner join dmpStatus dmp
	on pd.clientid = dmp.clientid
inner join disbursements d
	on pd.clientid = d.clientid
inner join receipts r
	on pd.clientid = r.clientid
inner join 
	(
	select ri.ClientID, ri.PaymentAmount, ri.datePaymentMade
	from dbo.Receipts ri
	inner join 
		(
		select rii.ClientID, max( rii.datePaymentMade )
		from dbo.Receipts rii
		group by rii.ClientID
		) lri
		on lri.ClientID = ri.ClientID 
		and lri.datePaymentMade = ri.datePaymentMade
	where ri.datePaymentMade
	) lr
	on lr.ClientID = pd.ClientID
where
	pd.activeStatus IS NOT NULL
order by
	pd.clientlastname asc

Open in new window

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 20399678
Error: Line 68: Incorrect syntax near ')'. (State:37000, Native Code: AA)
That's some query.  Thanks for your help.  Where is the syntax error?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 35

Expert Comment

by:David Todd
ID: 20399763
Hi,

Sorry about that.

Delete line 67.

Corrected script below.

Cheers
  David

PS Explanation on the script.
The inner most query finds the latest date for each client.
The next query uses this to add the amount for that payment.
I don't see the need for the distinct keyword, but you are already sorting the data so it mightn't have much impact.

Do compare the query plans between my script and yours. Sometimes the more elegant solution isn't actually any faster, and can be quite a bit slower.
select 
	distinct( pd.clientid )
	, pd.clientfirstname
	, pd.clientlastname
	, c.creditorid
	, c.creditorname
	, dw.id
	, dw.accountnumber
	, dw.balanceCurrent
	, dw.balanceInitial
	, lr.paymentAmount
	, lr.datePaymentMade
from personalData pd
inner join debtWorksheet dw
	on pd.clientid = dw.clientid
inner join creditors c
	on dw.creditorid = c.creditorid
inner join dmpStatus dmp
	on pd.clientid = dmp.clientid
inner join disbursements d
	on pd.clientid = d.clientid
inner join receipts r
	on pd.clientid = r.clientid
inner join 
	(
	select ri.ClientID, ri.PaymentAmount, ri.datePaymentMade
	from dbo.Receipts ri
	inner join 
		(
		select rii.ClientID, max( rii.datePaymentMade )
		from dbo.Receipts rii
		group by rii.ClientID
		) lri
		on lri.ClientID = ri.ClientID 
		and lri.datePaymentMade = ri.datePaymentMade
	) lr
	on lr.ClientID = pd.ClientID
where
	pd.activeStatus IS NOT NULL
order by
	pd.clientlastname asc

Open in new window

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 20399825
Thanks.  I go this when I executed the query.

Error: No column was specified for column 2 of 'lri'. (State:37000, Native Code: 1FDB)

0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 20400237
Hi,

Sorry, line 30 should be
            select rii.ClientID, max( rii.datePaymentMade ) as LastDatePaymentMade

and line 35 should be
            and lri.LastDatePaymentMade = ri.datePaymentMade

HTH
  David
select 
	distinct( pd.clientid )
	, pd.clientfirstname
	, pd.clientlastname
	, c.creditorid
	, c.creditorname
	, dw.id
	, dw.accountnumber
	, dw.balanceCurrent
	, dw.balanceInitial
	, lr.paymentAmount
	, lr.datePaymentMade
from personalData pd
inner join debtWorksheet dw
	on pd.clientid = dw.clientid
inner join creditors c
	on dw.creditorid = c.creditorid
inner join dmpStatus dmp
	on pd.clientid = dmp.clientid
inner join disbursements d
	on pd.clientid = d.clientid
inner join receipts r
	on pd.clientid = r.clientid
inner join 
	(
	select ri.ClientID, ri.PaymentAmount, ri.datePaymentMade
	from dbo.Receipts ri
	inner join 
		(
		select rii.ClientID, max( rii.datePaymentMade ) as LastDatePaymentMade
		from dbo.Receipts rii
		group by rii.ClientID
		) lri
		on lri.ClientID = ri.ClientID 
		and lri.LastDatePaymentMade = ri.datePaymentMade
	) lr
	on lr.ClientID = pd.ClientID
where
	pd.activeStatus IS NOT NULL
order by
	pd.clientlastname asc

Open in new window

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 20400280
Thank you for your help.  The query works great and is taking 1/20th the time to execute.

Points awarded.  You rock!

Craig
0
 
LVL 1

Author Closing Comment

by:CraigDerington
ID: 31412462
One of the best written queries I have seen yet....
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20400291
Thanks for the points Craig, and the comments!
 
Cheers
  David
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question