Calculate business days

Would have to include Holidays too.. but first

How do I use the UDF to calculate the number of days between DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSIT?

thx

Would have to include Holidays too.. but first
Then all the more reason to create a table. If you are worried for a minute that it may prove big, think again.  30 years worth of dates represents less than 8000 rows,   Then consider that each date value takes up 3 bytes and you will quickly see that even with a clustered index we are talking about a very small table.

if holidays needs to be considered, check this other question where I answered: https://www.experts-exchange.com/questions/28646359/SQL-to-work-out-if-there-is-5-work-days-in-a-week-or-4-work-days.html

How do I query the table - to determine the diff of days? between the 2 fields?
thx

on the last past:

--Step 3. Query business days
DECLARE @YourDateFrom DATETIME = '2015-03-30'
DECLARE @YourDateFrom DATETIME = '2015-04-16'
;
WITH CTEDates AS (
	SELECT @date1 AS dt

	UNION ALL

	SELECT DATEADD(dd, 1, dt)
	FROM CTEDates s
	WHERE @YourDateFrom <= @YourDateTo
)

SELECT COUNT(*) FROM CTEDates WHERE NOT EXISTS (SELECT * FROM dbo.tblHolidays WHERE Holiday = dt)

Select allOpen in new window

http://mobile.experts-exchange.com/Database/MS-SQL-Server/A_12318-MSSQL-Calculating-Age-in-years-right-v-wrong.html

Try that article.  It is possible to exclude  weekends by a calculation.  If you must also accout for other holidays then you do need a table.

Oh and there is at least one article on  using a calendar for this by Jim Horn

Still not clear on a possible solution.
I create table of dates (include holidays)  Maybe of All WORKDAYS or NONWORK DAYS
Then I query the table getting the DateDiff?

Something like below:

SELECT
    DATEDIFF(DAY, DATE_CHECK_RECEIVED, DATE_CHECK_DEPOSITED) -
    (SELECT COUNT(*)
     FROM your_dates_table ydt
     WHERE ydt.date BETWEEN mdt.DATE_CHECK_RECEIVED and mdt.DATE_CHECK_DEPOSITED
         AND ydt.holiday = 1
    ) AS total_work_days
FROM main_data_table mdt

it is way easier just to store the holidays in the table, there are only 10-12 a year.

Easier and more efficient than this:
SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

Really?

Either query is trivial to write.  But I still strongly prefer a NonWorkDays table.  I want to be able to add a description for why it's not a WorkDay, which is more natural with a couple of NonWorkDays tables (one to control with generic dates/reasons and one for the actual list of dates).

As I noted, if required, you might still need some type of date range calendar that describes fiscal weeks / quarters / years, but that is a separate requirement from knowing individual work day or not.

I agree with ScottPletcher. I prefer to maintain 10-12 entries in a table each year rather than 365!

Actually I admit I typically add weekends to the table, so I have another ~104 entries per year.  However, we have different clients, and they each can have a customized calendar, and some day.  [Oddly, to me at least, for at least one of them, Monday is not a shipping day, for whatever reason.]

I'm still not clear on what the SQL should be - if  have a table of Workday table or Non Workday table.
thx

I'm still not clear on what the SQL should be - if  have a table of Workday table or Non Workday table.
If you build a table called WorkDays with (you guessed it)  all the working days then the SQL is as simple as:

SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

Select allOpen in new window

???????????????????????????????????????????
How do I calculate the number of workingdays between DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

i have 1 table of workingdays.. and another table with the checks - DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

I give up.

thanks..!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 I'm trying this..  But math is off
I have several records with   DATE_CHECK_RECEIVED = '2015-01-05' (MONDAY)
DATE_CHECK_DEPOSITED= '2015-01-07'  (WED)  - I GET dayes_to_deposit = 3?

My workdays tables has these days ....


-- Using this approach

If you only stored working days instead:

select
  DATE_CHECK_RECEIVED
, DATE_CHECK_DEPOSITED

, (
        select count(*) from
       from MyCalendarTable
       where caldate between yt.DATE_CHECK_RECEIVED and yt.DATE_CHECK_DEPOSITED
 ) as dayes_to_deposit
 
from Your_Table YT

What is the answer you are expecting? You currently get 3 because the query return January 5, 6, and 7. If you want to get 2, you could do "Count(*)-1"

thx everyone..........