DATEDIFF calculation 1

[LloydDobler]

Hope someone can help out with this one. We are trying to calculate days lost, and it works with the code below, however we don't want weekends to be included in the calcualtion. Is this possible?

USE HRDATABASE
SELECT DATEDIFF(day, injurydate, returntoworkdate) AS no_of_days_lost
FROM accidenttable

 

[ClaireHCM]

You should query a stored proc /function to do the task

alter proc workdays @date1 datetime ,@date2 datetime
as
set nocount on
declare @d1 int
declare @d2 int

declare @diff int

if @date1 = @date2 or @date1 > @date2
begin
RAISERROR ('Your first date should be earlier than second date.', 16, 1)
set @diff = 0
end

else
begin
set @d1 = 6-datepart(dw,@date1)
if @d1 < 0
begin
set @d1 = 0
end
set @d2 = datepart(dw,@date2)-1
if @d2 = 6
begin
set @d2 = 5
end

if datediff(d,@date1,@date2)-@d1-@d2 < 7
set @diff = @d1+@d2

if datediff(d,@date1,@date2)-@d1-@d2 > 7

set @diff = ((datediff(d,@date1,@date2)-@d1-@d2)/7)*5+@d1+@d2
end
select @diff as working_days


exec workdays '11/13/2003','12/27/2003'

 

[dan1967]

Try this:

USE HRDATABASE
SELECT DATEDIFF(day, injurydate, returntoworkdate) - ((DATEPART(dw, injurydate)+DATEDIFF(day, injurydate, returntoworkdate))/7*2) AS no_of_days_lost
FROM accidenttable

What we did was take the DATEDIFF of the 2 dates, and subtract from it the calculation for weekends. To remove weekends, we are getting the day value (1-7) for the injurydate and adding it to the DATEDIFF. We are then dividing by 7 (days in a week) and multiplying by 2 (days in the weekend.

So, if the injury date was Tue 7/8 and the returntoworkdate was Mon 7/14:

7-((3+7)/7*2) = 5

Explained:

3+7 = 10
10/7 = 1 (integer only calculation)
1*2 = 2
7-2=5 number of workdays missed.

Now this won't work for holidays, only Sat and Sun.

Hope this helps.

Dan.

 

[sunila7]

Hi Dan,

Nice Solution... Another way would be with similar logic

Select datediff(day,injurydate,returntoworkdate)-
(datediff(day,injurydate -datepart(dw,injurydate)+ 2,returntoworkdate -datepart(dw,returntoworkdate)+ 2)/7)*2
FROM accidenttable

This also will not work if injury date begins on a weekend...


Sunil

Sunil