en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
דצמ31

Written by: ronen ariely
31/12/2015 20:29 RssIcon

Simple CLR code for SQL Server

using System;
using System.IO;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Reflection;
 
[assembly: AssemblyVersion("0.0.0.1")]
[assembly: AssemblyFileVersion("0.0.0.1")]
[assembly: AssemblyDescription("Aggregate Percentage Function")]
[assembly: AssemblyCompany("Ronen Ariely")]
 
namespace RonenAriely
{
 
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
        //use clr serialization to serialize the intermediate result
        //Format.Native,
        Format.UserDefined,    
        IsInvariantToNulls          = true,                 //optimizer property
        IsInvariantToDuplicates     = false,                //optimizer property
        IsInvariantToOrder          = true,             //optimizer property
        MaxByteSize                 = -1                    //maximum size in bytes of persisted value
    )]
    public class ArielyPercentageAggCls : Microsoft.SqlServer.Server.IBinarySerialize
    {
        private decimal IntermediateResult;
 
        public void Init()
        {
            this.IntermediateResult = 0;
        }
 
        public void Accumulate(decimal _value)
        {
            IntermediateResult = (((IntermediateResult + 100) / 100) * _value); // Perc
        }
 
        ///
        public void Merge(ArielyPercentageAggCls other)
        {
            this.IntermediateResult = (((this.IntermediateResult + 100) / 100) * other.IntermediateResult); // Perc
        }
 
        public decimal Terminate()
        {
            return this.IntermediateResult;
        }
 
        bool _isNull;
        public bool IsNull
        {
            get { return _isNull; }
        }
 
        void IBinarySerialize.Write(System.IO.BinaryWriter w)
        {
            w.Write(IsNull);
 
            if (!IsNull){
                w.Write(IntermediateResult);
            }
        }
 
        void IBinarySerialize.Read(System.IO.BinaryReader r)
         
            _isNull = r.ReadBoolean();
            if (!IsNull){
                IntermediateResult = r.ReadDecimal();
            }
        }
    }
}

Demo:

DDL+DML

create database ArielyTestDB
GO
  
use ArielyTestDB
GO
  
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
  
--DROP AGGREGATE ArielyPercentageAgg
--DROP ASSEMBLY ArielyPercentageAggAssembly
--GO
  
CREATE ASSEMBLY ArielyPercentageAggAssembly
    FROM 'E:\ArielyPercentageAgg.dll'
    WITH PERMISSION_SET = SAFE;
GO
  
CREATE AGGREGATE ArielyPercentageAgg(@input decimal(32,2))
    RETURNS decimal(32,2)
    EXTERNAL NAME ArielyPercentageAggAssembly.[RonenAriely.ArielyPercentageAggCls];
GO
  
CREATE TABLE [dbo].[Increase]
    (
        [IncreaseID]      [INT] IDENTITY(1, 1) NOT NULL,
        [IncreaseDate]    [DATE] NOT NULL,
        [IncreasePercent] [MONEY] NOT NULL,
        CONSTRAINT [PK_Increase] PRIMARY KEY CLUSTERED ( [IncreaseID] ASC )
    )
GO
 
TRUNCATE TABLE [Increase]
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-04-01' AS Date), 5.0000)
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-09-01' AS Date), 10.0000)
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-11-01' AS Date), 7.0000)
GO
   
CREATE TABLE [dbo].[PayRoll]
    (
        [PayRollID]    [INT] IDENTITY(1, 1) NOT NULL,
        [EmployeeNo]   [INT] NOT NULL,
        [EmployeeName] [VARCHAR](8) NOT NULL,
        [Month]        [DATE] NOT NULL,
        [Salary]       [MONEY] NOT NULL,
        CONSTRAINT [PK_PayRoll] PRIMARY KEY CLUSTERED ( [PayRollID] ASC )
    )
GO
      
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-05-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000)
GO
  
SELECT * FROM [Increase]
SELECT * FROM [PayRoll]
GO

     


Solution:

SELECT
    o.PayRollID, o.EmployeeNo, o.EmployeeName, o.[Month], o.Salary
    , finalSalary =  o.Salary * ((  (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month]) + 100 ) / 100 )
    ,increasePercentFromBase = (SELECT dbo.ArielyPercentageAgg([IncreasePercent]) FROM [Increase] i where i.IncreaseDate <= o.[Month]) 
FROM PayRoll o
GO


Result: