Using DateDiff to work as Excel Networkdays

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

SQL Server 2012 Forums

Transact-SQL (2012)

Using DateDiff to work as Excel Networkdays

Author

Topic

ryankeast
Starting Member

9 Posts

Posted - 2015-01-05 : 03:57:34

Happy New Year to all.

So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)

When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.

Luckily there is another table in the database called - ih_non_work_days.

The format of the date is "2014-12-25 00:00:00.000" for example in that table.

How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?

So for now my basic script looks like -

SELECT   com.comm_reference AS 'Referance'
     ,com.current_task_target_date AS 'TargetDate'
             , DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-05 : 04:17:00

You can add a Where clause:

WHERE NOT EXISTS (SELECT 1 FROM ih_non_work_days.dbo.yourTable WHERE yourDateColumn = com.current_task_target_date)

--------------------
Rock n Roll with SQL

ryankeast
Starting Member

9 Posts

Posted - 2015-01-05 : 05:38:30

Thank you for your reply.

So I've added the Where Clause - but the calculation of the days has made no differance?

SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'

FROM [dbo].[em_communication] as com

WHERE NOT EXISTS (SELECT 1 FROM [dbo].[ih_non_work_days] WHERE date_off = com.current_task_target_date)

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-05 : 06:00:14

The Where clause will exclude records which are in ih_non_work_days. You need to cross-check the same; check a date available in ih_non_work_days and this date should be not be in the output.

--------------------
Rock n Roll with SQL

ryankeast
Starting Member

9 Posts

Posted - 2015-01-05 : 06:17:38

Again thanks for your message - but I'm afraid I don't understand what you are advising me to do.

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-05 : 06:25:20

Hi,

1. Is your requirement to exclude records from ih_non_work_days? If the answer is Yes then the above Where clause serves your purpose.
If the answer is No then please explain further about your requirement.

2. "but the calculation of the days has made no differance": This maybe be due to the fact that you are seeing the output of DATEDIFF (same as before) as those input values are not available in ih_non_work_days and is this not what your requirement is? Please correct me if my understanding is wrong.

--------------------
Rock n Roll with SQL

ryankeast
Starting Member

9 Posts

Posted - 2015-01-05 : 06:35:15

The requirement is in the DATEDIFF calculation is for the differance to not include any dates that would be the in ih_non_work_days database.

So using the following script below

SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'


FROM [dbo].[em_communication] as com


WHERE NOT EXISTS (SELECT 1 FROM [dbo].[ih_non_work_days] WHERE date_off = com.current_task_target_date)

One of the items returned is -
Referance - 25713
Target Date - 2015-01-02 00:00:00.000
Incorrect Date Calculation - 3

This shouldn't be 3 days overdue as 2 of those days were non working days.

Both those days appear in the ih_non_work_days. (2015-01-03 and 2015-01-04) So I would expect the DATEDIFF to calculate 1 instead of 3.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-05 : 07:44:14

To avoid time wasting, please in future post test data and expected output.
The test data should be consumable, dates should be in ISO format etc.

Try something like:


-- *** Test Data **
CREATE TABLE #ih_non_work_days
(
	non_work_day date NOT NULL
);
INSERT INTO #ih_non_work_days
VALUES ('20150110')
	,('20150111');
CREATE TABLE #em_communication
(
	comm_reference int NOT NULL
	,current_task_target_date date NOT NULL
);
INSERT INTO #em_communication
VALUES (1, '20150112')
	,(2, '20150116');
-- *** End Test Data **

SELECT C.comm_reference AS Referance
     ,C.current_task_target_date AS TargetDate
	,DATEDIFF(day, CURRENT_TIMESTAMP, C.current_task_target_date) - N.NoWorkDays WorkingDaysLeft
FROM #em_communication C
	CROSS APPLY
	(
		SELECT COUNT(*) AS NoWorkDays
		FROM #ih_non_work_days D
		WHERE D.non_work_day BETWEEN CURRENT_TIMESTAMP AND C.current_task_target_date
	) N;

ryankeast
Starting Member

9 Posts

Posted - 2015-01-05 : 09:48:42

Thanks so much - modified this slightly to the following and it works a treat -

SELECT C.comm_reference AS Referance
     ,C.current_task_target_date AS TargetDate
	,DATEDIFF(d, C.current_task_target_date,CURRENT_TIMESTAMP) - NoWorkDays AS 'Days Past Target Date'
FROM [dbo].[em_communication] C

CROSS APPLY
	(
		SELECT COUNT(*) AS NoWorkDays
		FROM [dbo].[ih_non_work_days] D
		WHERE D.date_off BETWEEN C.current_task_target_date AND CURRENT_TIMESTAMP
	) N;

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-05 : 09:58:23

Glad you got it to work.

A better way to handle this type of problem is to use a calendar table.

If you search for something like 'calendar table sql' you should find a number of articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources