Calculate DateTime difference in SQL Server with ITVF

If you work with Date types in SQL Server, calculating date difference is not simple because of the way DateDiff work. You need to write functions for calculating years difference between two dates, months difference, etc. You can do that creating like always scalar function that will do some calculations and return a value. If you use such a scalar function in a SELECT clause and that select returns large number of rows, you will soon realize that calculating scalar value will be significant part of the total execution time. Not just that, but also if you open profiler to see SQL batches, you will see all that calls to scalar functions for every row in the query as a row in the profiler, and with such a large number of rows in the profiler, you can miss the important ones. SQL Server since version 2000 have introduced a great feature called Inline Table Value Functions (iTVF). iTVF's are such an important feature because using them in combination with CROSS APPLY you can really have code reusability which is hard to achieve in any SQL language. If you do some complex columns calculations for example calculating some distributions, and you do that same calculations in many procedures, then creating ITVF and put the complex calculations can be your boost. After that changing the distribution and fixing bugs will be on one place only.
To return to the title and write functions that are very useful in any database. I will present two just to give you idea, from which you can create some others. First the function for calculating Years difference between two dates:

CREATE FUNCTION dbo.DateDiffYears
(
     @DateFrom AS DATETIME
     , @DateTo AS DATETIME
)
RETURNS TABLE
AS
     RETURN SELECT Years = DATEDIFF(yy, @DateFrom, @DateTo)
- CASE WHEN DATEPART(dy, @DateFrom) > DATEPART(dy, @DateTo) 
THEN 1 ELSE 0 END

Now you can use with CROSS APPLY in your queries. Imagine a table Persons that have a column BirthDate. You need to return their age in the result set. You will have something like this:
SELECT PersonId
       , Age = DY.Years
FROM   Persons
       CROSS APPLY dbo.DateDiffYears(BirthDate, GETDATE())

The second one is called DateSpan, not like .NET TimeSpan but if you need the same one, you can easely create one, which will return Years, Months and Days difference between two days:
CREATE FUNCTION dbo.DateSpan
(
       @DateFrom AS DATETIME
       , @DateTo AS DATETIME
)
RETURNS TABLE
AS
       RETURN SELECT Years, TotalMonths, Months = TotalMonths - Years * 12, TotalDays, [Days]
       FROM (
              Years = DATEDIFF(yy, @DateFrom, @DateTo) - CASE WHEN DATEPART(dy, @DateFrom) > DATEPART(dy, @DateTo) THEN 1 ELSE 0 END
              , TotalMonths = DATEDIFF(mm, @DateFrom, @DateTo) - CASE WHEN DATEPART(d, @DateFrom) > DATEPART(d, @DateTo) THEN 1 ELSE 0 END
              , TotalDays = DATEDIFF(dd, @DateFrom, @DateTo)
              , [Days] = ABS(DAY(@DateTo) - DAY(@DateFrom) + DAY(DATEADD(d, -DAY(@DateTo), @DateTo)))
       ) D
The performance difference between ITVF and scalar functions is big and lineary grows on number of rows, and you will not see any additional row in the profiler.

Calculate DateTime Difference in SQL Server

I have no idea, why still SQL Server have no support for TimeSpan type. Also in 2008 version will be so many new DateTime types but still functions for manipulating DateTime values are few. If you need to calculate difference between two dates, but that difference to be represented as years:months: days, you will realize that that is not an easy task. There is DateDiff function, but when you try to use it you will see that this function will not return desired result. At least to get the result in a easy way.

I was in a need for such a function so I start looking for on the net. I found one which was pretty much what i needed. However I decide to create my own, much simpler and just for what I need. So here it is:

CREATE FUNCTION [dbo].[TotalDateDiff]
(
        @DateFrom AS SMALLDATETIME,
        @DateTo AS SMALLDATETIME
)
RETURNS CHAR(8)
AS
BEGIN
    DECLARE @Result CHAR(8)
    DECLARE @Years SMALLINT, @Months SMALLINT, @Days SMALLINT

    SELECT    @Years = DATEDIFF(YEAR, @DateFrom, @DateTo),
            @Months = DATEDIFF(MONTH, @DateFrom, @DateTo)

    SET @Months = @Months - @Years * 12

    SET @Days = DAY(@DateTo) - DAY(@DateFrom)
    IF @Days < 0
        BEGIN
            SET @Months = @Months - 1
            SET @Days = DAY(DATEADD(DAY, -DAY(@DateTo), @DateTo)) + @Days
        END

    IF @Months < 0
        BEGIN
            SET @Years = @Years - 1
            SET @Months = 12 + @Months
        END

    SET @Result = RIGHT('0' + CAST(@Years AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Months AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Days AS VARCHAR(2)), 2)
    RETURN @Result
END

Visual C# MVP Award

Today I receive mail from Microsoft:

Congratulations! We are pleased to present you with the 2008 Microsoft® MVP Award! The MVP Award is our way to say thank you for promoting the spirit of community and improving people’s lives and the industry’s success every day. We appreciate your extraordinary efforts in Visual C# technical communities during the past year.

Thanks Microsoft, I really appreciate this award.

My efforts in past couple of years on msdn forums, in particular C#, ADO, SQL forums, for helping other developers is the main reason. My nickname is boban.s, so you can find some very useful posts from ones about base class libraries in .NET though posts related with windows application type of problems to threading, localization, application update etc.

You probably know that winning MVP award is a result of many activities in public community, writing books, blogs, managing user groups, etc. I would like to make this blog active in next year in order to get award for next year too. I will post mainly about C#, but also about T-SQL and SharePoint. I already have in mind what will be the next post.

SMA Technical Indicator

I saw a question on MSDN Forums about having real-time SMA indicator. So even if I never used this indicator in real systems, I know it's simplest one and decide to develop it. So here it is:

public class SMA
{
    private readonly int _Length;
    private readonly bool _StoreData;
    private decimal _Value;
    private decimal _Price;
    private bool _Primed;
    private readonly string _Name;
    private readonly DecimalCollection _PriceArray = new DecimalCollection();
    private readonly DecimalCollection _ValueArray = new DecimalCollection();

    public SMA(int length) : this(length, false)
    {
    }

    /// <summary>
    /// Class for calculating Simple Moving Average
    /// </summary>
    /// <param name="length">Lenght SMA calculation formula</param>
    /// <param name="storeData"></param>
    public SMA(int length, bool storeData)
    {
        _Length = length;
        _StoreData = storeData;
        _Name = GetType().Name + Length;
    }

    public int Length
    {
        get { return _Length; }
    }

    public bool StoreData
    {
        get { return _StoreData; }
    }

    public decimal Value
    {
        get { return _Value; }
    }

    public bool Primed
    {
        get { return _Primed; }
    }

    public string Name
    {
        get { return _Name; }
    }

    public DecimalCollection ValueArray
    {
        get { return _ValueArray; }
    }

    public void PriceTick(decimal price, bool add)
    {
        if (add)
            AddPrice(price);
        else
            EditPrice(price);
    }

    public void PriceTicks(DecimalCollection prices)
    {
        if (prices == null || prices.Count == 0)
            return;
        for (int i = 0; i < prices.Count; i++)
        {
            AddPrice(prices[i]);
        }
    }

    private void AddPrice(decimal price)
    {
        _Price = price;
        if (!_Primed)
        {
            _PriceArray.Add(_Price);
            if (_PriceArray.Count == Length)
            {
                _Primed = true;
                _Value = _PriceArray.Average();
            }
        }
        else
        {
            _PriceArray.RemoveAt(0);
            _PriceArray.Add(price);

            _Value = _PriceArray.Average();

            if (_StoreData)
            {
                ValueArray.Add(_Value);
            }
        }

    }

    private void EditPrice(decimal price)
    {
        if (price != _Price)
        {
            _Price = price;

            _PriceArray[_PriceArray.Count - 1] = _Price;

            if (_Primed)
            {
                if (_PriceArray.Count == _Length)
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
                else
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
            }
        }
    }
}

This source uses DecimalCollection class that is already published on my blog.

Password Generator

You have probably got in situations when you need a password generator, somethimes for generating keys like for generating ticket for thin client using webservices, or for your personal use when you need to create a key for user account, but want that to be randomly. Here is a good piece of code for generating simple or strong passwords.

public static string GeneratePassword(int passwordLen, bool includeSmallLetters, bool includeBigLetters,
                            bool includeNumbers, bool includeSpecialCharacters)
{
   char[] returnValue = new char[passwordLen];
   char[] Choises;
   string smallLetters = string.Empty;
   string bigLetters = string.Empty;
   string numbers = string.Empty;
   string specialcharacters = string.Empty;

   int choisesLen = 0;

   if (includeSmallLetters)
   {
      smallLetters = "abcdefghijklmnopqrstuvwxyz";
      choisesLen += smallLetters.Length;
   }
   if (includeBigLetters)
   {
      bigLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
      choisesLen += bigLetters.Length;
   }
   if (includeNumbers)
   {
      numbers = "012345678901234567890123456789";
      choisesLen += numbers.Length;
   }
   if (includeSpecialCharacters)
   {
      specialcharacters = "~`!@#$%^&*()-_+=\|<,>.?/ {[}]";
      choisesLen += specialcharacters.Length;
   }
   if (choisesLen == 0)
      throw new ArgumentOutOfRangeException("includeSmallLetters",
                                  "At least one type of characters must be included!");

   Choises = (smallLetters + bigLetters + numbers + specialcharacters).ToCharArray();
   Random rnd = new Random();

   for (int i = 0; i < passwordLen; i++)
   {
      returnValue[i] = Choises[rnd.Next(choisesLen - 1)];
   }
   return new string(returnValue);
}

Real-Time Technical Indicator

I develop trading systems. In most of systems, technical indicators are used. What is very important when you run hundreds of trading systems that do very intensive calculations on price change, is that executions must be very efficient and quick. I was using some library, and what was bothering me is that all such indicators library are static. What i mean by static is that you have a function for calculating some indicator and you need to send array of input values, and to receive a value or array as output. But if you receive a new value or a change of last value in the array, you need to send the whole array of values again, and calculations to be done once again for all that data. Not just that array of prices and volumes can be big in number (hundreds) but also the number of times when you need calculation can be a problem. Sometimes you receive very frequent price events and you need all that calculations for all systems.

For that purpose i start developing my library of real-time technical indicators, that will when possible, do just additional calculation for new or changed value and not all that calculation for previous values. So I don't have static methods and instead have class for every indicator.

Here is source for one of the simplest indicators named Accumulation/Distribution indicator or AD.

Formula for this indicator is:.
PreviousValue + ((Close - Low) - (High - Close))/ (High - Low) * Volume

public class AD
{
    private Decimal _Value;
    private readonly String _Name;
    private readonly BarDecimalCollection _PriceBars = new BarDecimalCollection();
    private readonly DecimalCollection _ValueArray = new DecimalCollection();

    /// <summary>
    /// Class for calculating Accumulation/Distribution
    /// </summary>
    public AD()
    {
        _Name = GetType().Name;
    }
    public Decimal Value
    {
        get { return _Value; }
    }

    public String Name
    {
        get { return _Name; }
    }

    public DecimalCollection ValueArray
    {
        get { return _ValueArray; }
    }

    /// <summary>
    /// Method for adding one BarDecimal struct instance
    /// </summary>
    /// <param name="priceBar"></param>
    public void PriceBar(BarDecimal priceBar)
    {
        if (priceBar.AddBar)
            AddPriceBar(priceBar);
        else
            EditPriceBar(priceBar);
    }

    /// <summary>
    /// Method for adding collection of BarDecimal struct instances
    /// </summary>
    /// <param name="priceBars"></param>
    public void PriceBars(BarDecimalCollection priceBars)
    {
        if (priceBars == null || priceBars.Count == 0)
            return;
        for (Int32 i = 0; i < priceBars.Count; i++)
        {
            AddPriceBar(priceBars[i]);
        }
    }

    private void AddPriceBar(BarDecimal priceBar)
    {
        _PriceBars.Add(priceBar);
        Decimal change = priceBar.HighPrice - priceBar.LowPrice;
        if (change != 0)
        {
            _Value = ((priceBar.ClosePrice - priceBar.LowPrice) - (priceBar.HighPrice - priceBar.ClosePrice)) / change * priceBar.Volume + _Value;
        }
        ValueArray.Add(_Value);
    }

    private void EditPriceBar(BarDecimal priceBar)
    {
        _PriceBars[_PriceBars.Count - 1] = priceBar;

        Decimal change = priceBar.HighPrice - priceBar.LowPrice;
        if (change != 0)
        {
            Decimal prevValue = ValueArray.Count > 1 ? ValueArray[ValueArray.Count - 2] : 0;
            _Value = ((priceBar.ClosePrice - priceBar.LowPrice) - (priceBar.HighPrice - priceBar.ClosePrice)) / change + prevValue;
        }
        ValueArray[ValueArray.Count - 1] = _Value;
    }
}

DecimalCollection

This is my DecimalCollection that i use in my Technical Indicators Library. I would like your comments on this source.

public class DecimalCollection : CollectionBase, IEnumerable<Decimal>, IEnumerator
{
   private Int32 _CurrentIndex = 0;

   public virtual void AddRange(Decimal[] items)
   {
      //Rule CA1062
      if (items == null) throw new ArgumentNullException("items");

      foreach (Decimal item in items)
      {
         List.Add(item);
      }
   }

   public virtual void AddRange(DecimalCollection items)
   {
      foreach (Decimal item in items)
      {
         List.Add(item);
      }
   }

   public virtual void Add(Decimal value)
   {
      List.Add(value);
   }

   public virtual Boolean Contains(Decimal value)
   {
      return List.Contains(value);
   }

   public virtual Int32 IndexOf(Decimal value)
   {
      return List.IndexOf(value);
   }

   public virtual void Insert(Int32 index, Decimal value)
   {
      List.Insert(index, value);
   }

   public virtual Decimal this[Int32 index]
   {
      get { return (Decimal) List[index]; }
      set { List[index] = value; }
   }

   public virtual void Remove(Decimal value)
   {
      List.Remove(value);
   }

   private Decimal Sum()
   {
      Decimal sum = 0m;
      for (Int32 i = 0; i < Count; i++)
         sum += this[i];
      return sum;
   }

   public virtual Decimal Average()
   {
      if (Count > 0)
         return Sum()/Count;
      else
         return 0m;
   }


   #region IEnumerable<Double> Members

   IEnumerator<Decimal> IEnumerable<Decimal>.GetEnumerator()
   {
      foreach (Decimal item in InnerList)
      {
         yield return item;
      }
   }

   #endregion

   #region IEnumerable Members

   IEnumerator IEnumerable.GetEnumerator()
   {
      return this;
   }

   #endregion

   #region IEnumerator Members

   public object Current
   {
      get { return this[_CurrentIndex]; }
   }

   public Boolean MoveNext()
   {
      if (_CurrentIndex < Count)
      {
         _CurrentIndex++;
         return true;
      }
      else
         return false;
   }

   public void Reset()
   {
      _CurrentIndex = 0;
   }

   #endregion
}