Generating a year prefixed serial number in SQL

An existing SQL function I was using created year-prefixed serial numbers. This year it eventually ran into a problem where it couldn’t go higher than 201502015. Here’s my complete rewrite of that function:

	DECLARE @SerialNumber int

	-- Get current year as string
	DECLARE @Year varchar(4) = YEAR(GETDATE())

	-- Get current max inmate number
	DECLARE @CurrentMax int
	SELECT	@CurrentMax = MAX(SerialNumber)
	FROM	Serials
	WHERE	SerialNumber LIKE @Year +'%'

	-- Remove only the first instance of the year in the max serial number
	DECLARE @NextSeq int
	SET		@NextSeq = Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

	-- Increment sequence number
	SET		@NextSeq = ISNULL(@NextSeq, 0) + 1

	-- Convert sequence number to string for concatenation
	DECLARE @NextStr varchar(5) = @NextSeq

	-- Return zero padded serial number as integer with year prefix
	SET		@SerialNumber = @Year + RIGHT('00000' + @NextStr, 5)

	RETURN @SerialNumber

The function above generates an incrementing zero padded serial number with the current year prefixed, as such:


What you want to avoid is using the Replace() function to trim out the year from the preceding serial number, because, well, it will Replace() all over that value like nobody’s business. Here I’m using

Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

to only trim out the first instance of the year.

Greatest-N-Per-Group SQL: Getting the latest location for each user

This algorithm comes up a lot in the system I’m building. Get the most recent whatever for each user, along with all their information. Also known as the greatest-n-per-group problem. Here’s the most efficient SQL algorithm I’ve come across:

SELECT   C.ClientId, C.LastName, L.LocationName AS CurrentLocation
FROM     Client C
	FROM (SELECT * FROM Location WHERE UpdateDate < @Date) AS t1
	LEFT JOIN (SELECT * FROM Location WHERE UpdateDate < @Date) AS t2
	ON t1.ClientId = t2.ClientId AND t1.UpdateDate < t2.UpdateDate
	WHERE	t2.ClientId IS NULL) AS L ON C.ClientId = L.ClientId

This query finds the latest location for each client as of the supplied @Date. The main part of the algorithm reads as:

Return the row t1 for which no other row t2 exists with the same ClientId and a greater UpdateDate

The outer left join allows us to return users with no entries in the location table.