CODEWITHSUNDEEP
c#sql-server-2008
nnnn
nnnn

Reputation: 1051

How to get 14 days prior to the given date avoiding holidays

In my system ,the due date of the bill must be 14 days after the issued date.
I have due date and I want to know issued date .
I have to calculate :

issued date = 14 days prior to the due date

but 14 days must be business days ,not holidays.
Holidays is stored in a table 'tblHolidayMaster' like this,

Date                   Description
2012/05/13         Mother's Day
2012/06/02         Saturnday
2012/12/25         Christmas

How can I calculate the issued date avoiding holidays?
Thank you for all of your interests and replies.

Upvotes: 5

Views: 1627

Answers (5)

MonMon
MonMon

Reputation: 45

I calculate the issued date avoid your holiday from your table 'tblHolidayMaster' only.

 int addDay = -14;
  DateTime dtInputDay = System.DateTime.Now;//Your input day
  DateTime dtResultDate = new DateTime();
  dtResultDate = dtInputDay.AddDays(addDay);
  bool result = false;
  string strExpression;
  DataView haveHoliday;
  while (!result) {
      strExpression = "Date >='" + Convert.ToDateTime(dtResultDate.ToString("yyyy/MM/dd")) + "' and Date <='" + Convert.ToDateTime(dtInputDay.ToString("yyyy/MM/dd")) + "'";
      haveHoliday = new DataView(tblHolidayMaster);
      haveHoliday.RowFilter = strExpression;
      if (haveHoliday.Count == 0) {          
        result = true;
      } else {
          addDay = -(haveHoliday.Count);
          dtInputDay = dtResultDate.AddDays(-1);
          dtResultDate = dtResultDate.AddDays(addDay);
      }
  }

Your issued date is dtResultDate

Upvotes: 1

CodesInChaos
CodesInChaos

Reputation: 108830

I went with the straight forward looping solution, so it will be slow for long intervals. But for short intervals like 14 days, it should be quite fast.

You need to pass in the holidays in the constructor. An instance of BusinessDays is immutable and can be reused. In practice you probably will use an IoC singleton or a similar construct to get it.

AddBusinessDays throws an ArgumentException if the start date is a non business day, since you didn't specify how to treat that case. In particular AddBusinessDays(0) on a non business day would have strange properties otherwise. It'd either break time reversal symmetry, or return a non business day.

public class BusinessDays
{
    private HashSet<DateTime> holidaySet;
    public ReadOnlyCollection<DayOfWeek> WeekendDays{get; private set;}

    public BusinessDays(IEnumerable<DateTime> holidays, IEnumerable<DayOfWeek> weekendDays)
    {
        WeekendDays = new ReadOnlyCollection<DayOfWeek>(weekendDays.Distinct().OrderBy(x=>x).ToArray());
        if(holidays.Any(d => d != d.Date))
            throw new ArgumentException("holidays", "A date must have time set to midnight");
        holidaySet = new HashSet<DateTime>(holidays);
    }

    public BusinessDays(IEnumerable<DateTime> holidays)
        :this(holidays, new[]{DayOfWeek.Saturday, DayOfWeek.Sunday})
    {
    }

    public bool IsWeekend(DayOfWeek dayOfWeek)
    {
        return WeekendDays.Contains(dayOfWeek);
    }

    public bool IsWeekend(DateTime date)
    {
        return IsWeekend(date.DayOfWeek);
    }

    public bool IsHoliday(DateTime date)
    {
        return holidaySet.Contains(date.Date);
    }

    public bool IsBusinessDay(DateTime date)
    {
        return !IsWeekend(date) && !IsHoliday(date);
    }

    public DateTime AddBusinessDays(DateTime date, int days)
    {
        if(!IsBusinessDay(date))
            throw new ArgumentException("date", "date bust be a business day");
        int sign = Math.Sign(days);
        while(days != 0)
        {
            do
            {
              date.AddDays(sign);
            } while(!IsBusinessDay(date));
            days -= sign;
        }
        return date;
    }
}

Upvotes: 2

Talha
Talha

Reputation: 19262

I think that is what you required. It is simple and I have tested it and it is working... And it is not a bad approach to write a function or SP in databases rather to write the complex code in C#... (change column name of date as in your db.)

Make it function or SP as what you want.

Note: Comment the check of 'Saturday' and 'Sunday'. If it is already added in your table reocrds.

declare @NextWorkingDate datetime
declare @CurrentDate datetime
set @CurrentDate = GETDATE()
set @NextWorkingDate = @CurrentDate 
declare @i int = 0
While(@i < 14)
Begin
    if(((select COUNT(*) from dbo.tblHolidayMaster where convert(varchar(10),[Date],101) like convert(varchar(10),@NextWorkingDate,101)) > 0) OR DATENAME(WEEKDAY,@NextWorkingDate) = 'Saturday' OR DATENAME(WEEKDAY,@NextWorkingDate) = 'Sunday')
    Begin
        print 'a '  
        print @NextWorkingDate
        set @NextWorkingDate = @NextWorkingDate + 1
        CONTINUE
    End
    else
    Begin
        print  'b ' 
        print @NextWorkingDate
        set @NextWorkingDate = @NextWorkingDate + 1
        set @i = @i + 1
        CONTINUE
    End
End
print @NextWorkingDate

Upvotes: 1

Ketchup
Ketchup

Reputation: 3111

I would calculate the Date using a function like the one below (which i use)

public static DateTime AddBusinessDays(DateTime date, int days)
 {
    if (days == 0) return date;

   if (date.DayOfWeek == DayOfWeek.Saturday)
   {
    date = date.AddDays(2);
    days -= 1;
  }
  else if (date.DayOfWeek == DayOfWeek.Sunday)
  {
    date = date.AddDays(1);
    days -= 1;
  } 



 date = date.AddDays(days / 5 * 7);
 int extraDays = days % 5;

 if ((int)date.DayOfWeek + extraDays > 5)
 {
    extraDays += 2;
 }

int extraDaysForHolidays =-1;
//Load holidays from DB into list
List<DateTime> dates = GetHolidays();

while(extraDaysForHolidays !=0)
{

 var days =  dates.Where(x => x >= date  && x <= date.AddDays(extraDays)).Count;
 extraDaysForHolidays =days;
 extraDays+=days;  
}


return date.AddDays(extraDays);

}

Haven't tested the ast section that does the holidays

Upvotes: 3

Rajesh Subramanian
Rajesh Subramanian

Reputation: 6490

Try the following link,

http://www.c-sharpcorner.com/uploadfile/tirthacs/difference-between-two-dates-excluding-weekends/

Upvotes: 0

Related Questions