how to calculate number of days between two dates excluding weekends

[ravikumarmeduri]

Dear All,

Require your support to write a query in sql server.

I have a table (report) with Country, Status, Open Date, Close date.

Values in country is - India, USA, UK, etc..,

status is - Open, Closed

Open date, close date - is in date and time format

I have to prepare a query with below the conditions.
1) how to calculate no.of days between two dates excluding holidays
2) Holidays are different from country to country (e.g., Sat,Sun or Fri, Sat)
3) If the status is Open, then no.of days difference=current date - open date(excluding holidays)
4)If the status is closed, then no.of days difference=closed date - open date (excluding holidays)

Any help will be highly appreciated.