Looking for a set-based solution

Question

Post reply

Looking for a set-based solution

Scott-144766

SSCarpal Tunnel

Points: 4333
More actions
January 8, 2010 at 7:02 am

#141585

SQL 2005 SP3
I have a list of customers and associated transactions, but some of the transactions are null. Where a customer has only one non-null transaction number I want to update all the nulls for that customer with that number. Where a customer has more than one different non-null transaction, I shouldn't make any changes, as shown in the examples below. I can make this work with a cursor but it's slow. Before I get started on a solution with lots of subqueries, can anyone see a less laborious way of attacking this?
Starting with
Customer Trx
A1 123
A1 NULL
A1 NULL
A2 124
A2 125
A2 NULL
A3 126
A3 126
A3 NULL
A4 NULL
A4 NULL
A5 127
A5 127
A5 127
Desired result
Customer Trx
A1 123
A1 123
A1 123
A2 124
A2 125
A2 NULL
A3 126
A3 126
A3 126
A4 NULL
A4 NULL
A5 127
A5 127
A5 127
Thanks
Scott
--
Scott

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Scott

Run this 'ere bit of code, and the answer will come to you in a flash:

SELECT Customer, COUNT(*) AS trxTotal, COUNT(trx) AS trxNonNULL

FROM YourTable

GROUP BY Customer

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Scott-144766 (1/8/2010)
SQL 2005 SP3
I have a list of customers and associated transactions, but some of the transactions are null. Where a customer has only one non-null transaction number I want to update all the nulls for that customer with that number. Where a customer has more than one different non-null transaction, I shouldn't make any changes, as shown in the examples below. I can make this work with a cursor but it's slow. Before I get started on a solution with lots of subqueries, can anyone see a less laborious way of attacking this?
Starting with
Customer Trx
A1 123
A1 NULL
A1 NULL
A2 124
A2 125
A2 NULL
A3 126
A3 126
A3 NULL
A4 NULL
A4 NULL
A5 127
A5 127
A5 127
Desired result
Customer Trx
A1 123
A1 123
A1 123
A2 124
A2 125
A2 NULL
A3 126
A3 126
A3 126
A4 NULL
A4 NULL
A5 127
A5 127
A5 127
Thanks
Scott

Why is the 3rd A2 null in the expected results instead of 125?

Mark Cowne One Orange Chip Points: 26945 More actions · Answer 3

WITH CTE AS (

SELECT Customer, MAX(Trx) AS Trx

FROM MyTable

WHERE Trx IS NOT NULL

GROUP BY Customer

HAVING COUNT(DISTINCT Trx)=1)

UPDATE m

SET Trx=c.Trx

FROM MyTable m

INNER JOIN CTE c ON c.Customer=m.Customer

WHERE m.Trx IS NULL;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 4

Lynn Pettis (1/8/2010)
Scott-144766 (1/8/2010)
Starting with
Customer Trx
A2 124
A2 125
A2 NULL
Desired result
Customer Trx
A2 124
A2 125
A2 NULL
Why is the 3rd A2 null in the expected results instead of 125?

Because customer A2 has two different transaction numbers - 124 and 125

--
Scott

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 5

Mark-101232 (1/8/2010)
WITH CTE AS (
SELECT Customer, MAX(Trx) AS Trx
FROM MyTable
WHERE Trx IS NOT NULL
GROUP BY Customer
HAVING COUNT(DISTINCT Trx)=1)
UPDATE m
SET Trx=c.Trx
FROM MyTable m
INNER JOIN CTE c ON c.Customer=m.Customer
WHERE m.Trx IS NULL;

I like this one - thanks Mark. I haven't seen COUNT(DISTINCT expression) before, so I've learned more than one thing today 🙂

--
Scott