remove extra record - 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

General SQL Server Forums

New to SQL Server Programming

remove extra record

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-20 : 10:34:00

I have a table sample:

DECLARE @Sample TABLE (Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20),
NewOrder INT, cdate datetime)

INSERT @Sample
SELECT 'a', 1, 'new', 1,'1/1/2003' UNION ALL
SELECT 'a', 2, 'renewal',2,'8/2/2003' UNION ALL
SELECT 'a', 3, 'renewal', 3, '2/1/2004'UNION ALL
SELECT 'a', 4, 'renewal', 4,'11/1/2004' UNion ALL
SELECT 'a', 5, 'renewal' ,5, '3/8/2006'UNION ALL
SELECT 'a', 6, 'new', 1, '3/1/2004' UNION ALL
SELECT 'a', 7, 'new', 1,'12/3/2005' UNION ALL
SELECT 'a', 8, 'renewal',2, '8/2/2005'UNION ALL
SELECT 'a', 9, 'renewal', 3,'9/2/2006'UNION ALL
SELECT 'a', 10, 'renewal',4, '10/2/2006'UNION ALL
SELECT 'a', 11, 'renewal', 5, '11/2/2006' UNION ALL
SELECT 'b', 1, 'new', 1,'8/2/2003'UNION ALL
SELECT 'b', 2, 'new', 1,'9/2/2003'UNION ALL
SELECT 'b', 3, 'new', 1,'8/2/2005'UNION ALL
SELECT 'c', 1, 'new', 1,'8/2/2004'UNION ALL
SELECT 'c', 2, 'renewal',2,'8/2/2005' UNION ALL
SELECT 'c', 3, 'renewal',3,'9/2/2005' UNION ALL
SELECT 'd', 2, 'new',1 ,'8/2/2005'UNION ALL
SELECT 'd', 3, 'renewal',2, '4/2/2006'UNION ALL
SELECT 'd', 4, 'renewal',3, '6/2/2006'UNION ALL
SELECT 'd', 4, 'renewal',4,'9/2/2006'

I want to remve all records serials startign with type 'new' (neworder 1) which is later than ,1/1/2005' and subsquncial renewals
The desire result should be:

Merchant OldOrder OrderType NewOrder cdate
a 1 new 1 2003-01-01 00:00:00.000
a 2 renewal 2 2003-08-02 00:00:00.000
a 3 renewal 3 2004-02-01 00:00:00.000
a 4 renewal 4 2004-11-01 00:00:00.000
a 5 renewal 5 2006-03-08 00:00:00.000
a 6 new 1 2004-03-01 00:00:00.000
b 1 new 1 2003-08-02 00:00:00.000
b 2 new 1 2003-09-02 00:00:00.000
c 1 new 1 2004-08-02 00:00:00.000
c 2 renewal 2 2005-08-02 00:00:00.000
c 3 renewal 3 2005-09-02 00:00:00.000
Thanks.
Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 10:48:17

Something like this

DELETE		s
FROM		@Sample AS s
INNER JOIN	(
			SELECT		Merchant,
					MIN(OldOrder) AS c
			FROM		@Sample
			WHERE		OrderType = 'new'
					AND cdate >= '20050101'
			GROUP BY	Merchant
		) AS x ON x.Merchant = s.Merchant AND x.c <= s.OldOrder

I think now it is time I get a cut of yourpay check...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80817
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80826

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources