View Blog


A bit of SQL

Mar 24 2010

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 WHEN ISNULL(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
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


Total: 1 Comment(s)
Peter Wilson
  Hmmm... just a bit hey?
· reply ·