Another "being selective about what to return" query

Transact-SQL
1 
DECLARE @test AS TABLE
(
TDate VARCHAR (8),
CustID INT,
RefID VARCHAR (10),
Value DECIMAL (15, 2),
TranType VARCHAR (20)
)
INSERT INTO @test
VALUES 
(
'20160301', '32990', '100234', '100.50', 'New Purchase'
)
INSERT INTO @test
VALUES 
(
'20160311', '32990', '100299', '145.50', 'New Purchase' 
)
INSERT INTO @test
VALUES 
(
'20160329', '32990', '100777', '174.50', 'New Purchase' 
)
SELECT * FROM @test

In the above example, I want to keep the first transaction as "New Purchase" but the two subsequent entries need to be flagged as "Existing Customer Purchase".

CustID always relates to one customer and RefID is the primary key, increasing sequentially.

Any pointers much appreciated.

2 
SELECT *,
	CASE 
		WHEN (refId = MIN(refId) OVER(PARTITION BY CustId)) THEN 'New Purchase'
		ELSE 'Exiting Customer Purchase'
	END
FROM @test
1 Like
3

Very good, thank you.