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"...

Someone had a lot of fun writing that query.

I believe that error means that there are unequal numbers of columns in the different SELECTs - so things just don't match...

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

Steady on, don't overdo it :hehe:

mikes84 (12/11/2012)

---

Hi,

I have a FULL OUTER JOIN with an OR condition that's running very slowly. The reason is because I'm trying to join a users table (id, username1, username2) where...

Eugene Elutin (12/11/2012)

---

Phil Parkin (12/11/2012)

---

WolfgangE (12/11/2012)

---

You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not...

WolfgangE (12/11/2012)

---

You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in...

?? looks remarkably like a lowercase version of my solution 🙂

cidr (12/11/2012)

---

Hi there,

Apologies for the elusive subject title. I'm trying to filter only rows on one field only when another field has a certain value.

I have a...

select *

,GroupNumber = (

case

when HouseNumber <= 214

then 1

when HouseNumber <= 238

then 2

...

Jeff's code is not wasted, do not worry.

But there is only so much that we can do to help - now you need to help yourself & do some study/learning.

10e5x (12/10/2012)

---

Hi Phil,

I shall try your approach first since u sounds very confident. And I believe u totally know your stuff. But sad to say, I am not up till...

This is a most entertaining thread.

I could help with the SSIS stuff (thanks Jeff!) - if it were needed, but it is not.

First task: write the proc to return the...

So many choices, I just can't decide which row I like best!

guoxiang1610 (12/9/2012)

---

Phil Parkin (12/9/2012)

---

May I ask how you think that SSIS can help you with this problem?

Based on the data provided, it looks like a stored proc would be suffucient...