January 2, 2015 at 4:38 am
Hi there,
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
January 2, 2015 at 4:53 am
You could use something like this one to get rid of weekends.
To exclude your other non-work days, simply
select count(*)
from ih_non_work_days
where [apply start and end date filter here]
And subtract this from the above.
With a bit of rework, this can all be done in a single hit.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 5, 2015 at 7:55 am
Thanks Phil -
Ended up doing the following which does what I need it to do.
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply