Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

Sub Select Query to get Last Payment and Date

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
CraigDerington
Asked:
CraigDerington
  • 4
  • 4
1 Solution
 
pai_prasadCommented:
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
 
David ToddSenior DBACommented:
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
 
CraigDeringtonAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
David ToddSenior DBACommented:
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
 
CraigDeringtonAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
CraigDeringtonAuthor Commented:
Thank you for your help.  The query works great and is taking 1/20th the time to execute.

Points awarded.  You rock!

Craig
0
 
CraigDeringtonAuthor Commented:
One of the best written queries I have seen yet....
0
 
David ToddSenior DBACommented:
Thanks for the points Craig, and the comments!
 
Cheers
  David
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now