How do I count Working Days only between two dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I count Working Days only between two dates

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-16 : 10:21:00

Hi guys,

I have the following simple SQL which counts the days difference between two date fields:

SELECT
DATEDIFF(dd,central_enquiry.enquiry_time, GETDATE()) as Days_Open

FROM
central_enquiry

---

How do I get it to exclude weekends?

I also have a table nonworkingdays which has a nonworking_date field where users can manually record national holidays and bank holidays etc.

Example date

nonworking_date
01/01/2014
18/04/2014
05/05/2014

How can I include this table in the calculation too?

Any info would be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 10:24:49

you can use calendar table if present in your db for that.
if you dont have one, use the below


SELECT COUNT(*)
FROM dbo.CalendarTable (startdate,enddate,1,0)

CalendarTable function can be found in below link
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-16 : 10:32:13

Hi Visakh, I don't have the CalenderTable in the database. As it is a 'read-only' query writing software I cannot use the table as it is not referenced.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 10:39:47

quote:
Originally posted by Maverick_

Hi Visakh, I don't have the CalenderTable in the database. As it is a 'read-only' query writing software I cannot use the table as it is not referenced.

then you've to give the logic inline in your query

ie like


SELECT SUM(CASE WHEN DATEADD(dd,v.number,central_enquiry.enquiry_time) % 7 <5 THEN 1 ELSE 0 END) AS WorkingDays
FROM central_enquiry ce
INNER JOIN master..spt_values v
ON v.type='p'
AND DATEADD(dd,v.number,ce.central_enquiry.enquiry_time) <=GETDATE()

If you've table with holiday details that should also be joined to above query as it only assumes Nonworking days as Sunday and Saturday in above case.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-16 : 10:47:03

Hi Visakh,

I get an error when I try to adapt your suggestion "The data types datetime and int are incompatible in the modulo operator."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 11:21:59

ah..thts a typo


SELECT SUM(CASE WHEN DATEDIFF(dd,v.number,central_enquiry.enquiry_time) % 7 <5 THEN 1 ELSE 0 END) AS WorkingDays
FROM central_enquiry ce
INNER JOIN master..spt_values v
ON v.type='p'
AND DATEADD(dd,v.number,ce.central_enquiry.enquiry_time) <=GETDATE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-16 : 11:38:22

Hi Visakh,

Thanks for this. I am trying to decode how this SQL works so I understand it. Why do you have % 7 in your SELECT statement.

I am also trying to understand what the entire bit of code after the INNER JOIN.

Do you mind breaking it down please? Sorry I have not come across this type of method before so I do not understand it :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 11:58:36

quote:
Originally posted by Maverick_

Hi Visakh,

Thanks for this. I am trying to decode how this SQL works so I understand it. Why do you have % 7 in your SELECT statement.

I am also trying to understand what the entire bit of code after the INNER JOIN.

Do you mind breaking it down please? Sorry I have not come across this type of method before so I do not understand it :)

To get day info
see

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

My method just uses an internal number table called spt_values to generate all dates between two days. Then it counts all except Saturdays and Sundays using % 7 logic
Another method is explained in above link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 04:47:42

Hi Visakh/everyone,

I still get not quite correct output with your example. For each enquiry I want to know how many working days it is open. Based on the example above I get the following output:

How do it to calculate how many working days an enquiry is open?

SELECT
ce.central_enquiry,
ce.enquiry_time
<calculation which gives how many days enquiry is open excluding non-working days> as days_open

FROM
central_enquiry ce

The date difference is between the ce.enquiry_time and getdate()

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 05:56:24

Hi all,

I got my own example working I think:

SELECT
ce.enquiry_number,
ce.enquiry_time,
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)

FROM
central_enquiry ce

---

I now also need it to deduct days recorded in the non_working days table I have.

The table is made up of:
nonworkingdaykey
nonworkingdate
nonworkingdatename

The data looks like this:

How can I get the dates from here exluded in my SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:27:14

quote:
Originally posted by Maverick_

Hi all,

I got my own example working I think:

SELECT
ce.enquiry_number,
ce.enquiry_time,
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM non_working_days WHERE nonworkingdate BETWEEN ce.enquiry_time AND dateadd(dd,datediff(dd,0,getdate()),0))
FROM
central_enquiry ce

---

I now also need it to deduct days recorded in the non_working days table I have.

The table is made up of:
nonworkingdaykey
nonworkingdate
nonworkingdatename

The data looks like this:

How can I get the dates from here exluded in my SQL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 07:35:24

Hi Visakh,

Thanks for this! The only remaining problem I have with the SQL is if the enquiry is logged on the day of a nonworking day, it counts it as part of it's calculations.

So for example if enquiry 123 was logged on 26/08/2013 (a bank holiday) then it counts the number of days as 82 days rather than 81.

Any way to get the SQL to handle enquiries which fell on a nonworking day (e.g. weekend or special holiday) to stop counting that day and start on next working day?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:48:42

quote:
Originally posted by Maverick_

Hi Visakh,

Thanks for this! The only remaining problem I have with the SQL is if the enquiry is logged on the day of a nonworking day, it counts it as part of it's calculations.

So for example if enquiry 123 was logged on 26/08/2013 (a bank holiday) then it counts the number of days as 82 days rather than 81.

Any way to get the SQL to handle enquiries which fell on a nonworking day (e.g. weekend or special holiday) to stop counting that day and start on next working day?

does ce.enquiry_time store timepart also?


SELECT
ce.enquiry_number,
ce.enquiry_time,
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM non_working_days WHERE nonworkingdate >= ce.enquiry_time AND nonworkingdate< dateadd(dd,datediff(dd,0,getdate()),1))
FROM
central_enquiry ce

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 07:52:41

Hi Visakh,

Yes it does.

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 08:00:12

When I try to insert this line of statement:
-(SELECT COUNT(*) FROM non_working_days WHERE nonworkingdate >= ce.enquiry_time AND nonworkingdate< dateadd(dd,datediff(dd,0,getdate()),1))

--

I still get the problem where it counts the nonworking day as part of it's count.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 08:08:39

quote:
Originally posted by Maverick_

When I try to insert this line of statement:
-(SELECT COUNT(*) FROM non_working_days WHERE nonworkingdate >= ce.enquiry_time AND nonworkingdate< dateadd(dd,datediff(dd,0,getdate()),1))

--

I still get the problem where it counts the nonworking day as part of it's count.

Let me understand this further
Doesnt ce.enquiry_time give you date and time when enquiry is logged ? or is it a different field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-12-17 : 08:17:59

Hi Visakh,

Correct. It logs both date and time in the same field.

See example data below with the field. The calculation currently states 246 working days, but this includes the 1st January result which should be discounted so it should come out as 245.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources