How to get 14 days prior to the given date avoiding holidays
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,
DateDescription
2012/05/13Mother's Day
2012/06/02Saturnday
2012/12/25Christmas
How can I calculate the issued date avoiding holidays?
Thank you for all of your interests and replies.
Answer by Rajesh Subramanian for How to get 14 days prior to the given date avoiding holidays
Try the following link,
http://www.c-sharpcorner.com/uploadfile/tirthacs/difference-between-two-dates-excluding-weekends/
Answer by Ketchup for How to get 14 days prior to the given date avoiding holidays
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 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
Answer by CodesInChaos for How to get 14 days prior to the given date avoiding holidays
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 holidaySet; public ReadOnlyCollection WeekendDays{get; private set;} public BusinessDays(IEnumerable holidays, IEnumerable weekendDays) { WeekendDays = new ReadOnlyCollection(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(holidays); } public BusinessDays(IEnumerable 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; } }
Answer by Talha for How to get 14 days prior to the given date avoiding holidays
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
Answer by MonMon for How to get 14 days prior to the given date avoiding holidays
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
Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72
0 comments:
Post a Comment