| Author |
 Topic  |
|
|
Dmh188
Starting Member
35 Posts |
 Posted - 08/15/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/15/2012 : 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
35 Posts |
Posted - 08/15/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/15/2012 : 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
India
47040 Posts |
Posted - 08/15/2012 : 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/
|
|
|
| |
Topic |
|
|
|
exclude weekends to get hours between dates
