Written by:
3/23/2010 3:12 PM 

With CTE_ActionDates (JobPKID, ActionPKID, ActionJobStatusLink, ActionDate1, ActionDate2) As
(Select tblActions.JobLink, tblActions.pkID As ActionPKID, tblActions.JobStatusLink, ActionDate As ActionDate1,
(Select Top 1 Actiondate From tblActions t2 Where t2.JobLink = tblActions.JobLink And t2.pkID > tblActions.pkid) As ActionDate2
From tblActions)

SELECT TOP 100 Percent
CTE_ActionDates.JobPKID, ActionPKID, ActionJobStatusLink,
CAST(SUM(tblDateLine.Workhours) * 60 * 60 - CASE WHEN CTE_ActionDates.ActionDate1 < MIN(tblDateLine.StartTime) THEN 0 ELSE DATEDIFF(s, MIN(tblDateLine.StartTime),
CTE_ActionDates.ActionDate1) END - CASE WHENISNULL(CTE_ActionDates.ActionDate2, GETDATE()) > MAX(tblDateLine.EndTime) THEN 0 ELSE DATEDIFF(s,
ISNULL(CTE_ActionDates.ActionDate2, GETDATE()), MAX(tblDateLine.EndTime)) END AS INT) AS OfficeTimeAgeInSeconds
FROM tblDateLine INNER JOIN
CTE_ActionDates ON tblDateLine.EndTime >= CTE_ActionDates.ActionDate1 AND tblDateLine.StartTime <= ISNULL(CTE_ActionDates.ActionDate2, GETDATE())
WHERE (tblDateLine.Workhours > 0) AND (tblDateLine.IsHoliday IS NULL)
GROUP BY CTE_ActionDates.JobPKID, CTE_ActionDates.ActionPKID, CTE_ActionDates.ActionJobStatusLink, CTE_ActionDates.ActionDate1, CTE_ActionDates.ActionDate2
Order By CTE_ActionDates.JobPKID, CTE_ActionDates.ActionPKID, CTE_ActionDates.ActionJobStatusLink

TrackbackPrint

Tags:

Categories:

Location: BlogsPRD Software blogs

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel