Using SQL Server CLR to add "earliest" and "latest" aggregate functions
Whilst there are undoubtedly very funky ways to achieve the same result, many moons ago I wrote a couple of SQL CLR aggregate functions for retrieving the earliest and latest values for a given column, i.e. given a table that contains two columns (IdValue INT and Stamp DATETIME) you can write the following query:
SELECT EARLIEST(IdValue, Stamp) [EarliestIdValue],
LATEST(IdValue, Stamp) [LatestIdValue]
FROM [dbo].[MyTable]
Which would, using this sample data:
IdValue | Stamp |
1 | 2017-01-01 00:00:00.000 |
2 | 2017-01-01 01:00:00.000 |
4 | 2017-01-01 02:00:00.000 |
3 | 2017-01-01 03:00:00.000 |
Give a result of:
EarliestIdValue | LatestIdValue |
1 | 3 |
This is subtly different to MIN/MAX as it bases the IdValue returned on the Stamp column, thus is rather useful for querying temporal data such as logging, e.g. "What's the earliest [Thing] that was accessed by User Y". This comes into its own more-so when you can have out-of-order logging occuring (disparate systems pushing log information meaning that you can't "just" order by an identity field and use MIN/MAX) or when you're aggregating other data as it allows for a relatively clean query to be written which makes future maintenance easier.
The Earliest CLR function code:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Runtime.InteropServices; [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedAggregate(Format.Native, IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, Name = "Earliest")] public partial class Earliest { private SqlDateTime earliestDateTime; private SqlInt32 earliestValue; public void Init() { earliestDateTime = SqlDateTime.Null; earliestValue = SqlInt32.Null; } public void Accumulate(SqlDateTime recordDateTime, SqlInt32 value) { if (earliestDateTime.IsNull) { earliestDateTime = recordDateTime; earliestValue = value; } else { if (recordDateTime < earliestDateTime) { earliestDateTime = recordDateTime; earliestValue = value; } } } public void Merge(Earliest value) { if ((value.earliestDateTime < earliestDateTime) || (earliestDateTime.IsNull)) { earliestValue = value.earliestValue; earliestDateTime = value.earliestDateTime; } } public SqlInt32 Terminate() { return earliestValue; } };
The Latest CLR function code:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Runtime.InteropServices; [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedAggregate(Format.Native, IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, Name = "Latest")] public partial class Latest { private SqlDateTime latestDateTime; private SqlInt32 latestValue; public void Init() { latestDateTime = SqlDateTime.Null; latestValue = SqlInt32.Null; } public void Accumulate(SqlDateTime recordDateTime, SqlInt32 value) { if (latestDateTime.IsNull) { latestDateTime = recordDateTime; latestValue = value; } else { if (recordDateTime > latestDateTime) { latestDateTime = recordDateTime; latestValue = value; } } } public void Merge(Latest value) { if ((value.latestDateTime > latestDateTime) || (latestDateTime.IsNull)) { latestValue = value.latestValue; latestDateTime = value.latestDateTime; } } public SqlInt32 Terminate() { return latestValue; } };
The two functions are basically mirrors of each other, with a simple swap of a greater than / less than in the Accumulate and Merge functions.