Solved

Sub Select Query to get Last Payment and Date

Posted on 2007-12-03
9
792 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:CraigDerington
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
One of the best written queries I have seen yet....
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Thanks for the points Craig, and the comments!
 
Cheers
  David
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now