Hi, I found the following to return mon-fri days between 2 dates but need help to amend it so that it takes in to account public holidays from the Holiday table. SELECT top 100 application_id, application_date as "Start date", status_date as "End Date", (DATEDIFF(dd, application_date, status_date) + 1) -(DATEDIFF(wk, application_date, status_date) * 2) -( CASE WHEN DATENAME(dw, status_date) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, status_date) = 'Saturday' THEN 1 ELSE 0 END) as "Difference" from applications Holiday table (holiday_date): 2007-12-26 00:00:00.000 2008-01-01 00:00:00.000 2008-03-21 00:00:00.000 2008-03-24 00:00:00.000 Thanks in advance Vijay

Hi Vijay, Try the below SQL query, should get you the number of weekdays between the application_date and the status_date without considering the holidays in between: SELECT TOP 100 application_id, application_date as "Start date", status_date as "End Date", (DATEDIFF(dd, application_date, status_date) + 1) -(DATEDIFF(wk, application_date, status_date) * 2) -(CASE WHEN DATENAME(dw, status_date) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, status_date) = 'Saturday' THEN 1 ELSE 0 END) -( /*Subtract the number of holidays that are not Saturdays or Sundays in the date range in context*/ SELECT COUNT (Holiday_Date) FROM Holiday WHERE Holiday_Date BETWEEN application_date AND status_date AND DATENAME (DW, Holiday_Date) NOT IN ('Saturday', 'Sunday') ) as "Difference" FROM applications Let me know if this does not work for you. HTH. Cheers, IceQB Please mark correct answers :)