Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sub Select Query to get Last Payment and Date

Posted on 2007-12-03
9
Medium Priority
?
823 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

604 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