Help with selecting data based on duplicates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with selecting data based on duplicates?

Author

Topic

Roach
Starting Member

5 Posts

Posted - 2009-06-25 : 15:30:33

I have the following dataset below. There are duplicate vouchers showing (well they aren’t actually duplicate because they are different location IDs). Im trying to form some logic so that my last column (blah) will take the column labeled ‘curr trx am’ all the time, UNLESS there is a duplicate (such as the case in voucher 24227 and 25250), in which case I want to use the column ‘doc amt’ for the data

I was thinking something like
blah = case when count(vchrnmbr) > 1 then docamt else currtrxam

but this is obviously not working.
Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-25 : 15:40:52

will the 'doc amt' be the same for all the duplicates? Or do you want to choose a specific doc amt to update.

Roach
Starting Member

5 Posts

Posted - 2009-06-25 : 15:46:09

No not necessarily. I want my new column to choose from the existing data, but it has to have some logic to determine if the row is a duplicate voucher or not. The ones that have a count > 1 needs to look at the lines DOCAMNT field and the ones that are unique, the CURTRXAM field.

I cant assume the docamnt will always be the same #. But ultimately the data is already there, I just need the logic for it to pick and choose which it will use. The first 8 lines should be DOCAMNT, and 9-14 should be CURTRXAM in this example.

Thanks!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-25 : 16:03:11

Try this..
But am sure someone will provide a better solution soon..

update a
set a.blah = coalesce(b.docamt,c.curtxam)
from TableA a
left join (select vchrnmbr,max(docamt) as docamt from TableA group by vchrnmbr having count(vchrnmbr) > 1) b on a.vchrnmbr = b.vchrnmbr
left join (select vchrnmbr,max(curtrxam) as curtxam from TableA group by vchrnmbr having count(vchrnmbr) = 1) c on a.vchrnmbr = c.vchrnmbr

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-25 : 16:58:02

I kinda glazed over the requirements, but I think this works:

DECLARE @Table TABLE 
(
	VendorID INT, 
	VchrNmbr VARCHAR(10), 
	CurtRxAm MONEY, 
	DocAmnt MONEY, 
	LocationID INT,
	Blah MONEY
)


INSERT
	@Table
SELECT 4690, '24227', $5.12, $50.54, 1, $0.0
UNION ALL SELECT 4690, '24227', $5.13, $50.54, 2, $0.0
UNION ALL SELECT 4690, '25250', $5.14, $50.54, 1, $0.0
UNION ALL SELECT 4690, '25250', $5.15, $50.54, 1, $0.0
UNION ALL SELECT 4690, '25250', $5.16, $50.54, 2, $0.0
UNION ALL SELECT 4690, '25251', $5.17, $50.54, 1, $0.0
UNION ALL SELECT 4690, '25252', $5.18, $50.54, 2, $0.0

UPDATE
	T
SET
	Blah = CASE 
			WHEN NumRows > 1 THEN DocAmnt
			ELSE CurtRxAm
		   END
FROM
	@Table AS T
INNER JOIN
	(
		SELECT
			VendorID,
			VchrNmbr,
			COUNT(*) AS NumRows
		FROM 
			@Table
		GROUP BY
			VendorID,
			VchrNmbr
	) AS D
	ON T.VendorID = D.VendorID
	AND T.VchrNmbr = D.VchrNmbr
	
SELECT *
FROM @Table

Roach
Starting Member

5 Posts

Posted - 2009-06-25 : 17:24:36

that worked, thanks so much!

Roach
Starting Member

5 Posts

Posted - 2009-06-26 : 15:57:49

Just to expand upon this, its working but not exactly. Using following SQL query:

left join (select vchrnmbr,max(docamt) as docamt from TableA group by vchrnmbr having count(vchrnmbr) > 1) b on a.vchrnmbr = b.vchrnmbr
left join (select vchrnmbr,max(curtrxam) as curtxam from TableA group by vchrnmbr having count(vchrnmbr) = 1) c on a.vchrnmbr = c.vchrnmbr

The problem is the max function. I really dont want the min or the max, I want the value of that line. Whats happening, if there are 2 lines, say one for 50 and one 100, they both end up being 100. Help would be much appreciated.

Thanks

Roach
Starting Member

5 Posts

Posted - 2009-06-26 : 16:51:09

Actually I was able to get the 2nd one working. Thanks everyone,. you guys rock!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources