Hi..
I have a table with 2 date fields
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
###### 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.

Commented:

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.
0
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)
``````
0
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
0
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.
0
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
0
Author Commented:
How do I query the table - to determine the diff of days? between the 2 fields?
thx
0
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

FROM CTEDates s
WHERE @YourDateFrom <= @YourDateTo
)

SELECT COUNT(*) FROM CTEDates WHERE NOT EXISTS (SELECT * FROM dbo.tblHolidays WHERE Holiday = dt)
``````
0
freelancerCommented:
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
freelancerCommented:
Oh and there is at least one article on  using a calendar for this by Jim Horn
0
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:

- (SELECT COUNT(*) FROM NonWorkDays nwd
WHERE nwd.day BETWEEN tbl.date_received and tbl.date_processed)
0
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?
0
Senior DBACommented:
Something like below:

SELECT
(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
Senior .Net ConsultantCommented:
it is way easier just to store the holidays in the table, there are only 10-12 a year.
0
Commented:
Easier and more efficient than this:
SELECT COUNT(*) FROM WorkingDays WHERE WorkingDay BETWEEN DATE_CHECK_RECEIVED and DATE_CHECK_DEPOSITED

Really?
0
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.
0
Senior .Net ConsultantCommented:
I agree with ScottPletcher. I prefer to maintain 10-12 entries in a table each year rather than 365!
0
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.]
0
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
0
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
``````
0
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
0
Commented:
I give up.
0
freelancerCommented:
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_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_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_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_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

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_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
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"
0
Author 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.