Calculate business days

Hi..
I have a table with 2 date fields
DATE_CHECK_RECEIVED
DATE_CHECK_DEPOSITED

How do I calculate the number of BUSINESS DAYS between DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED
I want to exclude SAT and SUN.

thx
LVL 1
Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
What about holidays?

Your best bet is to create a table of business days.  It  then becomes a trivial exercise to calculate the number of days by doing
``````SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED
``````
.  Let me know if you would like to pursue this approach.
Senior .Net ConsultantCommented:
I have created a GetAllDates UDF to which you can pass a bit asking to exclude weekends:

``````--Step 1: create the GetAllDates function
IF OBJECT_ID('dbo.GetAllDates','TF') IS NOT NULL
DROP FUNCTION dbo.GetAllDates
GO

CREATE FUNCTION [dbo].[GetAllDates]
(
@StartDate DATETIME ,
@EndDate DATETIME ,
@SkipWeekend BIT
)
RETURNS @Dates TABLE
(
CalendarDate DATE
)
AS
BEGIN
WHILE @StartDate <= @EndDate
BEGIN
IF	( @SkipWeekend = 0 )
OR ( ( @SkipWeekend = 1 ) AND ( DATEPART(WEEKDAY, @StartDate) NOT IN ( 1, 7 ) ) )
BEGIN
INSERT  INTO @Dates ( CalendarDate )
SELECT  @StartDate;
END;

SET @StartDate = DATEADD(dd, 1, @StartDate);
END;

RETURN;
END;
GO

--return all days of last week
SELECT * from dbo.GetAllDates(GETDATE()-7, GETDATE(), 0)
--return all WEEK days of last week
SELECT * from dbo.GetAllDates(GETDATE()-7, GETDATE(), 1)
``````
Author Commented:
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
Commented:
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.
Senior .Net ConsultantCommented:
if holidays needs to be considered, check this other question where I answered: http://www.experts-exchange.com/questions/28646359/SQL-to-work-out-if-there-is-5-work-days-in-a-week-or-4-work-days.html
Author Commented:
How do I query the table - to determine the diff of days? between the 2 fields?
thx
Senior .Net ConsultantCommented:
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)
``````
EE Topic AdvisorCommented:
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.
EE Topic AdvisorCommented:
Oh and there is at least one article on  using a calendar for this by Jim Horn
Senior DBACommented:
I use a table of NonWorkDays only.  To me, there is no need for a full calendar table just to determine non-shipping days or other non-work days.

[You may, of course, need a fiscal calendar to assign date ranges to fiscal groupings, esp. for non-calendar methods, such as 4-4-5 weeks, etc..]

Then the day count becomes:

DATEDIFF(DAY, tbl.date_received, tbl.date_processed) + 1
- (SELECT COUNT(*) FROM NonWorkDays nwd
WHERE nwd.day BETWEEN tbl.date_received and tbl.date_processed)
Author Commented:
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?
Senior DBACommented:
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
Senior .Net ConsultantCommented:
it is way easier just to store the holidays in the table, there are only 10-12 a year.
Commented:
Easier and more efficient than this:
SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

Really?
Senior DBACommented:
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.
Senior .Net ConsultantCommented:
I agree with ScottPletcher. I prefer to maintain 10-12 entries in a table each year rather than 365!
Senior DBACommented:
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.]
Author Commented:
I'm still not clear on what the SQL should be - if  have a table of Workday table or Non Workday table.
thx
Commented:
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
``````
Author Commented:
???????????????????????????????????????????
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
Commented:
I give up.
EE Topic AdvisorCommented:
There is disagreement amongst us on the best method, but ignore that detail for now.

Imagine if that you do have a table with a record for every day (both working and non-working)
nb. I use a day/month/year pattern below and mmm/yyyy could mean any month of any year

myCalendarTable
caldate             isworking
1/mmm/yyyy  1
2/mmm/yyyy  1
3/mmm/yyyy  1
4/mmm/yyyy  1
5/mmm/yyyy  1
6/mmm/yyyy  0
4/mmm/yyyy  0
...
31/mmm/yyyy 1

select count(*)
from MyCalendarTable
where isworking = 1
and caldate between 1/mmm/yyyy and 7/mmm/yyyy

result = 5

i.e. for each row represents a day, where some field indicates if it is a working day or not, then a simple count of rows can provide the needed number of working days.

There is a design choice:
Store each day (largest table, but still small)
store only working days (approx 5/7 of the size of first option)
store only NON-weekend NON-working days (tiny table)

all will help to produce the needed calculation, the calculation just differs a bit

---------------------------------
A way to do the calculation is to use a "correlated subquery":

& if you stored all days in the calendar table:

select
DATE_CHECK_RECEIVED
, DATE_CHECK_DEPOSITED

, (
select count(*) from
from MyCalendarTable
where isworking = 1 and caldate between yt.DATE_CHECK_RECEIVED and yt.DATE_CHECK_DEPOSITED
) as dayes_to_deposit

from Your_Table YT

====================================

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

==========================

if you only stored non-working days it would be something like this

select
DATE_CHECK_RECEIVED
, DATE_CHECK_DEPOSITED

, (   datediff(day, DATE_CHECK_DEPOSITED, DATE_CHECK_RECEIVED) + 1
- 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

==============================
==============================

& another method is an OUTER APPLY instead of the correlated subquery, e.g.

select
DATE_CHECK_RECEIVED
, DATE_CHECK_DEPOSITED
, OA.days_to_deposit
from Your_Table YT
OUTER APPLY (
select count(*) from
from MyCalendarTable
where isworking = 1 and caldate between yt.DATE_CHECK_RECEIVED and yt.DATE_CHECK_DEPOSITED
) OA ( days_to_deposit  )
;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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
Senior .Net ConsultantCommented:
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"
Author Commented:
thx everyone..........
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.