If your requirement needs to include all saturadys then you can go this way
1)Subtract the two dates...you will get number of days
2) Week(date1)-Week(date2) ...this will give you the week difference
Every week has one sunday
3) From no of days subtract the week you will get number days without sunday.
Thanks for reply. This one is work find if we don't think about public holiday.
My requirement is count day exclude [Public Holiday] + [Sunday] from period.
Let see example:
ID StartDate EndDate 1 01-11-2011 15-11-2011
In this period have 2 sunday(on 6 and 13) and 1 public holiday (on 10). So working day should be 11.
Note: Public holiday is another one table seperate from above table.
Any idea besides this?
Firstly refer to my above post wherein you can calculate number of days excluding sunday.
Now to exclude public holiday here is the logic:
(Number of days) - (networkdays(date1,date2)-networkdays(date1,date2,holiday_list))
description is as follows:
networkdays(date1,date2) : this will return number of workdays
networkdays(date1,date2,holiday_list) : this will returns number of actual days
(Total_Number_of_days) - (workdays-actualdays) = days excluding sunday and public holidays