Filter a table - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Filter a table

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-05-09 : 11:30:50

I have a table with 4 columns (ID [primary key], Receipt, Policy, Batch). I want to filter those records have same receipt number, same policy number but different batch number.

I use group by clause for receipt number and policy number but how about batch number. If I aslo group by batch I can have the results I want. Please help me. Many Thanks!
Ex:
ID Policy Receipt Batch
1 123456 A523456 134521
2 201001 B223340 134521
3 201002 B223341 134523
4 456789 H123456 100012
5 209909 B223341 204012
6 101010 G123789 204012
7 101010 G123790 204017
8 123456 A523456 134521
9 456789 H123456 124509
10 456790 H123457 124509
11 456791 H123458 124509
12 456792 H123459 100012
13 100001 H123460 145678
14 100001 H123461 124509
15 100001 H123462 145678
16 123456 A523456 124509
17 123456 A523456 100012
18 321456 G999999 204012
19 410420 B123457 204012
20 100001 H123461 124509

The records I want is ID 13, 14, 16.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-09 : 12:36:41

Can you double check your data again or reiterate your requirements? I'm missing how you should get 13, 14 & 16.

webforyou
Starting Member

15 Posts

Posted - 2007-05-09 : 12:56:11

Sorry for missing in my solution:

"I use group by clause for receipt number and policy number but how about batch number. If I aslo group by batch I can NOT have the results I want. Please help me. Many Thanks!"

For simple data example (only 5 rows):
ID Policy Receipt Batch
1 123456 A523456 134521
2 123456 A523456 134521
3 123456 A523456 132329
4 456789 H123456 100012
5 209909 B223341 204012

I want you to care top three rows:
- ID 1 and ID 2 have the same policy, same receipt but also the same batch. So I do not want that two records
- ID 3 have the same Policy, same Receipt with ID 1 & 2 but different batch. So I want to filter this record.
- ID 4 & 5 do not have the same policy and same receipt with other rows so I don't care that rows.

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-09 : 13:11:57

Although this works for your simple example, I don't think it'll work for your real data. You might need to specify some more use cases, but try this:

--Set up
DECLARE @T TABLE(ID INT, Policy INT, Receipt VARCHAR(8), Batch INT)

INSERT @T
SELECT 1, 123456, 'A523456', 134521
UNION SELECT 2, 123456, 'A523456', 134521
UNION SELECT 3, 123456, 'A523456', 132329
UNION SELECT 4, 456789, 'H123456', 100012
UNION SELECT 5, 209909, 'B223341', 204012


--INSERT @T
--SELECT 1, 123456, 'A523456', 134521
--UNION SELECT 2, 201001, 'B223340', 134521
--UNION SELECT 3, 201002, 'B223341', 134523
--UNION SELECT 4, 456789, 'H123456', 100012
--UNION SELECT 5, 209909, 'B223341', 204012
--UNION SELECT 6, 101010, 'G123789', 204012
--UNION SELECT 7, 101010, 'G123790', 204017
--UNION SELECT 8, 123456, 'A523456', 134521
--UNION SELECT 9, 456789, 'H123456', 124509
--UNION SELECT 10, 456790, 'H123457', 124509
--UNION SELECT 11, 456791, 'H123458', 124509
--UNION SELECT 12, 456792, 'H123459', 100012
--UNION SELECT 13, 100001, 'H123460', 145678
--UNION SELECT 14, 100001, 'H123461', 124509
--UNION SELECT 15, 100001, 'H123462', 145678
--UNION SELECT 16, 123456, 'A523456', 124509
--UNION SELECT 17, 123456, 'A523456', 100012
--UNION SELECT 18, 321456, 'G999999', 204012
--UNION SELECT 19, 410420, 'B123457', 204012
--UNION SELECT 20, 100001, 'H123461', 124509

-- Query
SELECT 
	b.ID, 
	b.Policy, 
	b.Receipt, 
	b.Batch
FROM
	(
			SELECT Policy, Receipt, Batch
			FROM @T a
			GROUP BY Policy, Receipt, Batch
			HAVING COUNT(*) > 1
	) a
INNER JOIN
	@T b
	ON a.Policy = b.Policy
	AND a.Receipt = b.Receipt
WHERE
	a.Batch <> b.Batch
GROUP BY
	 b.ID, 
	 b.Policy, 
	 b.Receipt, 
	 b.Batch

-- You may need a MIN depending on the data..???
SELECT 
	MIN(b.ID), 
	b.Policy, 
	b.Receipt, 
	(SELECT Batch FROM @T WHERE ID = MIN(b.ID)) AS Batch
FROM
	(
			SELECT Policy, Receipt, Batch
			FROM @T a
			GROUP BY Policy, Receipt, Batch
			HAVING COUNT(*) > 1
	) a
INNER JOIN
	@T b
	ON a.Policy = b.Policy
	AND a.Receipt = b.Receipt
WHERE
	a.Batch <> b.Batch
GROUP BY
	 b.Policy, b.Receipt

-Ryan

EDIT: Aditional query

webforyou
Starting Member

15 Posts

Posted - 2007-05-10 : 11:06:02

Thanks for your reply. I will try more cases with real large data.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-11 : 02:19:22

Lamprey's query is efficient and will work only when policy, Receipt, batch is having a duplicate means..inner query which is looking for count(*) > 1 will not give any result for the following data
DECLARE @T TABLE(ID INT, Policy INT, Receipt VARCHAR(8), Batch INT)
INSERT @T
SELECT 1, 123456, 'A523456', 134528
UNION SELECT 2, 123456, 'A523456', 134521
UNION SELECT 3, 123456, 'A523456', 132329
UNION SELECT 4, 456789, 'H123456', 100012
UNION SELECT 5, 209909, 'B223341', 204012

for same policy 123456 and A523456 there different batches..try this....

Select Id, Policy, Receipt, Batch from
(SELECT
b.*, count = (Select count(*) from @t where Policy = b.Policy and Receipt = b.Receipt and batch = b.batch)
FROM (
SELECT Policy, Receipt
FROM @T a
GROUP BY Policy, Receipt
having count(*) > 1) as a, @t b where a.policy = b.policy and a.Receipt = b.Receipt) as d where count = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources