You need a function that return the number of working days between two date. but i suggest a CLR that it's faster for that type of computation.
[SqlFunction(SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlInt32 NETWORKDAYS(SqlDateTime start, SqlDateTime end, object weekend, SqlChars holidays)
{
if (start.IsNull || end.IsNull) return SqlInt32.Null;
int sign = start <= end ? 1 : -1;
if (start > end)
{
SqlDateTime tmp = start;
start = end;
end = tmp;
}
int stage1 = end.Value.Date.Subtract(start.Value.Date).Days + 1;
int d1 = (int) GetStartWeekOffsetforMonday(start.Value.DayOfWeek);
int d2 = (int)GetStartWeekOffsetforMonday(end.Value.DayOfWeek);
int weekendcount;
bool[] isweekend = GetWeekends(weekend, out weekendcount, "NETWORKDAYS");
int remnant = (stage1 - 1) % 7;
int stage2 = stage1 - weekendcount * (stage1 - remnant) / 7;
for (int i = d1; i <= d1 + remnant; i++) if (isweekend[i % 7]) stage2--;
if (holidays.IsNull) return stage2 * sign;
List<DateTime> hols = GetHolidays(holidays, "NETWORKDAYS");
foreach(DateTime hday in hols)
if (!isweekend[ GetStartWeekOffsetforMonday(hday.DayOfWeek)] && hday >= start.Value.Date && hday <= end.Value.Date) stage2--;
return stage2 * sign;
}
private static int GetStartWeekOffsetforMonday(DayOfWeek Day)
{
switch (Day)
{
case DayOfWeek.Monday: return 0;
case DayOfWeek.Tuesday: return 1;
case DayOfWeek.Wednesday: return 2;
case DayOfWeek.Thursday: return 3;
case DayOfWeek.Friday: return 4;
case DayOfWeek.Saturday: return 5;
case DayOfWeek.Sunday : return 6;
}
return 0;
}