Create user defined type, function and aggregate in SQL-Server 2008

In the previous artical, i have showed how to do these in sqlserver in 2005, now i will talk about it in 2008.

The most important difference is that in 2008, the Accumulate method can accept more than one parameters, so, the example in the last article can be modified to be more compact.

The source code is.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Runtime.InteropServices;

/**
** FAP-3418 Create SQL Server Function 'func_ AverageLastMonths' for 'star schema' sample queries
** The functions is used to calculate metrics like 'Rate1Month','Rate3Month','Rate1Year','Rate3Year','Rate5Year', 
** which can be found in all levels of calculation, such as Account level, FA level.etc
*/



///////////////////////////////////////////////////////////////////////////////////////////////////
// Cautions: plz remeber to serialize all variables in aggregate, if you choose UserDefined format.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
[StructLayout(LayoutKind.Sequential)]
public struct LastMonthRate: IBinarySerialize
{
    private List<double> rateInEachMonth;
    private int period;
    
    public void Write(System.IO.BinaryWriter w)
    {
        // Write out how many
        w.Write(period);
        w.Write(rateInEachMonth.Count);
        // Write out each element
        foreach (double rate in rateInEachMonth)
        {
            w.Write(rate);
        }
    }
    
    public void Read(System.IO.BinaryReader r)
    {
       rateInEachMonth = new List<double>();
       period = r.ReadInt32();
       Int32 ii = r.ReadInt32();
       
       for (int i = 1; i <= ii; i++)
       {
           double rate = r.ReadDouble();

           rateInEachMonth.Add(rate);
       }
    }

 
   public void Init()
    {
        rateInEachMonth = new List<double>();
        period = 0;
    }
 
   public void Accumulate(int yearMonth, double rate, int currentYear, int currentMonth, int period)
    {
       
       this.period = period;
       
       /// int currentYearMonth = (p.currentYear - 2000) * 100 + p.currentMonth;
       int thisYear = yearMonth/100 + 2000;
       int thisMonth = yearMonth%100;
       
       if (((currentYear-thisYear)*12 + currentMonth-thisMonth+1)<= period)
           rateInEachMonth.Add(rate);
       
    }
 
   public void Merge(LastMonthRate Group)
    {
        this.rateInEachMonth.AddRange(Group.rateInEachMonth);
    }

   public SqlDouble Terminate()
    {
        if(rateInEachMonth.Count != this.period) 
            return SqlDouble.Null;
        
        double res = 1.0;  /// != period case, can get result
        foreach (double rate in rateInEachMonth)
        {
            res *= (rate/100+1);
        }

        res -= 1;
        res *= 100;

        if (period > 12)
        {
            double t = res / 100 + 1;

            int sig = Math.Sign(t);

            double pow = Math.Pow(Math.Abs(t), (float)1 / (period / 12));

            res = sig * pow;

            res -= 1;

            res *= 100;
        }
        
        
        
        return (SqlDouble)(res);
    }

   
    
    
    
}

And the creation commands are also easier.

 

create assembly AverageLastMonths
from 'C:\Jia_Function\AverageLastMonths.dll'
with permission_set=safe;


create Aggregate func_AverageLastMonths
(
@yearMonth nvarchar(20), @rate float, @currentYear nvarchar(20), @currentMonth nvarchar(20), @period int
)
returns nvarchar(400)
external name [AverageLastMonths].[LastMonthRate];

So, it can be used like this:

 

select AccountID, dbo.func_AverageLastMonths(t.yearMonth, absolutereturnpctnet, 2010,5,3) as Rate3Months
from AccountMonthFact as am
join Time as t
on am.TimeNodeID = t.TimeNodeID and am.absolutereturnpctnet is not null
group by AccountID;