using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native, Name = "SimpleDiff", IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = false, IsNullIfEmpty = true)]
public struct SimpleDiff
{
    private int _count;
    private int _first;
    private int _last;

    public void Init()
    {
        _count = _first = _last = 0;
    }

    public void Accumulate(SqlInt32 Value)
    {
        if (Value.IsNull)
            return;

        if (Value.Value < _first)
            _first = Value.Value;
        else
            if (Value.Value > _last)
            _last = Value.Value;

        _count += 1;
    }

    public void Merge (SimpleDiff Group)
    {
        if (Group._first < _first)
            _first = Group._first;
        else
            if (Group._last > _last)
            _last = Group._last;
    }

    public SqlInt32 Terminate ()
    {
        return (_count == 0)? new SqlInt32() : new SqlInt32(_last - _first);
    }
};

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native, Name = "SimpleDiffOrdered", IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true)]
public struct SimpleDiffOrdered
{
    private int _count;
    private int _first;
    private int _last;
    private int _firstvalue;
    private int _lastvalue;

    public void Init()
    {
        _count = _firstvalue = _lastvalue = 0;
    }

    public void Accumulate(SqlInt32 Value, SqlInt32 Order)
    {
        if (Value.IsNull || Order.IsNull)
            return;

        if (_count == 0)
        {
            _first = Order.Value;
            _firstvalue = Value.Value;
            _last = Order.Value;
            _lastvalue = Value.Value;
        }
        else
            if (Order.Value < _first)
            {
                _first = Order.Value;
                _firstvalue = Value.Value;
            }
            else
                if (Order.Value > _last)
                {
                   _last = Order.Value;
                   _lastvalue = Value.Value;
                };

        _count += 1;
    }

    public void Merge(SimpleDiffOrdered Group)
    {
        if (Group._count == 0)
            return;

        if (Group._first < _first)
        {
            _first = Group._first;
            _firstvalue = Group._firstvalue;
        }
        else
            if (Group._last > _last)
            {
                _last = Group._last;
                _lastvalue = Group._lastvalue;
            }
    }

    public SqlInt32 Terminate()
    {
        return (_count == 0) ? new SqlInt32() : new SqlInt32(_lastvalue - _firstvalue);
    }
}

if exists(select 1 from sys.configurations where name = N'clr strict security' and value = 1)
 begin
  exec sp_configure N'clr strict security', 0;
  reconfigure with override;
 end;
go

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

if object_id('dbo.aggSimpleDiff', 'AF') is not null
 drop aggregate dbo.aggSimpleDiff;

if object_id('dbo.aggSimpleDiffOrdered', 'AF') is not null
 drop aggregate dbo.aggSimpleDiffOrdered;

if exists(select 1 from sys.assemblies where name = N'CLRAggregfates')
 drop assembly CLRAggregfates;
go

if exists(select 1 from sys.assemblies where name = N'CLRAggregates')
 drop assembly CLRAggregates;
go

create assembly CLRAggregates from '...';
go

create aggregate dbo.aggSimpleDiff 
(
 @Value int
) 
returns int  
external name CLRAggregates.SimpleDiff;
go

create aggregate dbo.aggSimpleDiffOrdered
(
 @Value int,
 @Order int
) 
returns int  
external name CLRAggregates.SimpleDiffOrdered;
go

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t (id int identity primary key, v int, g int, o int);
insert into dbo.t
 (v, g, o)
 select top(2000000)
  rand(checksum(newid())) * 10000,
  rand(checksum(newid())) * 10,
  rand(checksum(newid())) * 1000000
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

if object_id('dbo.fnAgg', 'IF') is not null
 drop function dbo.fnAgg;
go

create function dbo.fnAgg
(
 /*
  0: avg
  1: max - min
  2: max - min ordered 
  3: last_value - first_value ordered
 */
 @AggType tinyint
)
returns table
as
return (
 select
  t.g, avg(t.v) as agg
 from
  dbo.t t
 where
  @AggType = 0
 group by
  t.g

 union all

 select
  t.g, max(t.v) - min(t.v)
 from
  dbo.t t
 where
  @AggType = 1
 group by
  t.g

 union all

 select
  t.g, max(cast((cast(t.o as binary(4)) + 0x00000000) | t.v as bigint)) - min(cast((cast(t.o as binary(4)) + 0x00000000) | t.v as bigint))
 from
  dbo.t t
 where
  @AggType = 2
 group by
  t.g

 union all

 select
  t.g, t.lv - t.fv
 from
  (
   select
    g,
    first_value(v) over (partition by g order by o),
    last_value(v) over (partition by g order by o),
    row_number() over (partition by g order by o)
   from
    dbo.t
   where
    @AggType = 3
  ) t(g, fv, lv, rn)
 where
  t.rn = 1
);
go

declare @g int, @agg int, @show_plan bit = 0, @create_index bit = 0;

if @create_index = 1
 create index IX_t__g__o on dbo.t (g, o) include (v);

if @show_plan = 1
 set statistics xml on;
set statistics time on;

select @g = g, @agg = agg from dbo.fnAgg(0/*avg*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(1/*max - min*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(2/*max - min ordered*/) option (maxdop 1);
select @g = g, @agg = agg from dbo.fnAgg(3/*last_value - first_value ordered*/) option (maxdop 1);

select @g = g, @agg = dbo.aggSimpleDiff(v) from dbo.t group by g option (maxdop 1);
select @g = g, @agg = dbo.aggSimpleDiffOrdered(v, o) from dbo.t group by g option (maxdop 1);
go

set statistics time off;
set statistics xml off;
go

drop table dbo.t;
go