exclude weekends to get hours between 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

exclude weekends to get hours between dates

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2012-08-15 : 11:11:25

Hi there!
Need some help on this. I have been looking for days but still cant get this to work. I have 2 date ranges. First date shows when some received something for work and the second date is when they finished it. I need to calculate the hours it took to complete. But exclude weekends and holidays. I have a table made called Corporate_Calendar where i have all dates for 20 years, and if it is a weekend or holiday then the Holiday column is set to 0. If it is a working day then WorkDay is set to 1.
This is the query i was using that didn't exclude weekend and holidays:
select cm.orderno, sr.documentid, sr.assigneddate, sr.completeddate, sr.completedby,
-datediff(hh,sr.completeddate,sr.assigneddate) as hours_to_complete from shippingreview sr
inner join customerordermaster cm on cm.id=sr.documentid
where sr.reviewtype =1
and sr.assigneddate >=@s
and sr.assigneddate <=@e

The time stamps for assigneddate and completeddate are datetime.
The calendar table fields are Date, Holiday, and Workday. The date field is also datetime but the time stamp is 00:00:00:::

I have been trying for a day now to join the 2 so i can exclude the weekends and holidays, to then get an actual number of hours

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 11:35:57

could just need some extra columns in the calendar table

select cm.orderno, sr.documentid, sr.assigneddate, sr.completeddate, sr.completedby,
(select count(*) from Corporate_Calendar cc where cc.dte between sr.assigneddate and sr.completeddate and cc.WorkDay = 1) * 24 as hours_to_complete
from shippingreview sr
inner join customerordermaster cm on cm.id=sr.documentid
where sr.reviewtype =1
and sr.assigneddate >=@s
and sr.assigneddate <=@e

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dmh188
Starting Member

37 Posts

Posted - 2012-08-15 : 11:40:06

nigelrivett

Thank you for the reply. That sort of worked. However if the work was done in than less a day it puts the hours at 0. if its more then a day but less than 2 days, it put it as 24 hours. and if took more than 48 hours, but less then 72 hours, it put it at 48 hours. etc etc. it doesnt put the actual amount of hours

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-15 : 11:40:41

My thoughts on how to do it. Untested, and may be there are simpler ways but here it is:

SELECT
	cm.orderno,
	sr.documentid,
	sr.assigneddate,
	sr.completeddate,
	sr.completedby,
	SUM(DATEDIFF(hh,
		CASE 
			WHEN cc.DateColumn < sr.assigneddate THEN sr.assigneddate
			ELSE cc.DateColumn
		END,
		CASE 
			WHEN cc.DateColumn > sr.completeddate THEN sr.completeddate
			ELSE DATEADD(dd,1,cc.DateColumn)
		END
	)
	) AS hours_to_complete
			
FROM
	Corporate_Calendar	cc
	INNER JOIN shippingreview sr ON 
		cc.DateColumn <= CAST(sr.completeddate AS DATE) 
		AND cc.DateColumn >= CAST(sr.assigneddate AS DATE)
	INNER JOIN customerordermaster cm
		ON  cm.id = sr.documentid
WHERE
	sr.reviewtype = 1
	AND sr.assigneddate >= @s
	AND sr.assigneddate <= @e
	AND cc.WorkingDay = 1
GROUP BY
	cm.orderno,
	sr.documentid,
	sr.assigneddate,
	sr.completeddate,
	sr.completedby

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 11:44:07

whats the work hours for a day? do you've it stored somewhere? or is it that you consider entire hours for a day?


select cm.orderno, sr.documentid, sr.assigneddate, sr.completeddate, sr.completedby,
datediff(hh,sr.assigneddate,sr.completeddate)- (coalesce(cnt,0) * workhoursfield) as hours_to_complete 
from shippingreview sr
inner join customerordermaster cm on cm.id=sr.documentid
outer apply (select count(1) as cnt
             from calendartable
             where date >=sr.assigneddate
             and date <sr.completeddate+1
             and workday=0 and holiday=0
            )h
where sr.reviewtype =1
and sr.assigneddate >=@s
and sr.assigneddate <=@e

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources