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
JElsterAsked:
Who is Participating?
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.

Anthony PerkinsCommented:
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

Open in new window

.  Let me know if you would like to pursue this approach.
0
Éric MoreauSenior .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)

Open in new window

0
JElsterAuthor 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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Anthony PerkinsCommented:
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.
0
Éric MoreauSenior .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
0
JElsterAuthor Commented:
How do I query the table - to determine the diff of days? between the 2 fields?
thx
0
Éric MoreauSenior .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)

Open in new window

0
PortletPaulfreelancerCommented:
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.
0
PortletPaulfreelancerCommented:
Oh and there is at least one article on  using a calendar for this by Jim Horn
0
Scott PletcherSenior 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)
0
JElsterAuthor 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?
0
Scott PletcherSenior 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
0
Éric MoreauSenior .Net ConsultantCommented:
it is way easier just to store the holidays in the table, there are only 10-12 a year.
0
Anthony PerkinsCommented:
Easier and more efficient than this:
SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

Really?
0
Scott PletcherSenior 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.
0
Éric MoreauSenior .Net ConsultantCommented:
I agree with ScottPletcher. I prefer to maintain 10-12 entries in a table each year rather than 365!
0
Scott PletcherSenior 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.]
0
JElsterAuthor Commented:
I'm still not clear on what the SQL should be - if  have a table of Workday table or Non Workday table.
thx
0
Anthony PerkinsCommented:
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

Open in new window

0
JElsterAuthor 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
0
Anthony PerkinsCommented:
I give up.
0
PortletPaulfreelancerCommented:
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  )              
;
0

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.

Start your 7-day free trial
JElsterAuthor 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
0
Éric MoreauSenior .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"
0
JElsterAuthor Commented:
thx everyone..........
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.