?
Solved

Sub Select Query to get Last Payment and Date

Posted on 2007-12-03
9
Medium Priority
?
818 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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