Phil Parkin (12/14/2012)

---

Something like this (untested) should get you started.

with invs
as (
	select CustKey
		,sum(balance) invbalance
	from tarInvoice
	group by CustKey
	)
	,payments
as (
	select CustKey
		,Sum(UnappliedAmt) UnappliedAmt
	from tarCustPmt
	group by CustKey
	)
select c.CustKey
	,c.CustId
	,etc etc
	,IsNull(invs.invbalance, 0)
	,isNull(payments.UnappliedAmt)
from tarCustomer c
left join invs on c.CustKey = invs.CustKey
left join payments on c.CustKey = payments.CustKey

Your problem is that you are doing a many-to-many join. Avoid that by using subqueries which do separate aggregations and then joining on the results.

--Edit: I also recommend that you use meaningful aliases (not a,b,c etc) makes your query easier to read. And do you understand the implications of using the NOLOCK hint?

--Edit 2 - fixed typo

Lisa Liel (12/14/2012)

---

Thanks, Phil. I thought NOLOCK prevented table locks, and made the code run faster. Is that not the case?

Also... doing the subqueries with "With X as" instead of doing subqueries... does that prevent SQL from running that query over and over? Is that new?

Thanks again,
Lisa

Sean Lange (12/14/2012)

---

I will second Phil on the NOLOCK. That query hint is by no means a go fast pill. It is a get your data quick as long as you don't care about duplicates, missing rows, rolled back transactions and various other quirks that are nearly impossible to debug.

Here are a few links on the topic that I keep handy.

Lisa Liel (12/15/2012)

---

Thanks, guys. I'll read through the links. The fact is, in my particular context, speed is more important, and things like rolled back transactions are rare enough not to outweigh that. But there may be other circumstances, and I'm glad to know that it isn't as simple as I'd thought.