Need help with job-duration query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with job-duration query

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-01-24 : 13:49:25

I have a query that displays number of hours duration in a jobs by months matrix. Here is a simplified version of my tables.

CREATE TABLE jobhead (
jobnum varchar (14) NULL,
jobcompletiondate datetime NULL
)

CREATE TABLE labordtl (
jobnum varchar (14) NULL ,
clockindate datetime NULL ,
clockintime decimal(6, 2) NULL ,
)
Here's an abbreviated query.


SELECT 		h.JobNum, 
		(CASE WHEN MONTH(h.JobCompletionDate) = 1  THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS JAN,
		(CASE WHEN MONTH(h.JobCompletionDate) = 2  THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS FEB,
		...
FROM 		JobHead h 
		INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE 		JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
		AND l.ClockInTime<>0 
GROUP BY	h.JobNum
		,h.JobCompletionDate

The query shows, for each job, the month in which the job completed, and the number of hours it took to complete. I’m calculating the number of days’ duration by doing a datediff between the oldest and newest clockindates. I need to ignore adjustment transactions in the labordtl table – these rows are easily identified as they have clockintime values of 0. So far, so good. Now here’s my problem.

There are some jobs which have only one “real” labor transaction – this could happen if the job only took one day to complete. Other labor transactions may exist for that job, but let's say they are adjustments which we can ignore -- the date they were entered should not extend the duration of the job. In this situation, my datediff between the oldest valid transaction and the newest, returns 0. I don’t have to count hours between clockintime and clockouttime. The rule is simply that if there is only one "real" labor transaction, I need to count this as a 1 day job.

I thought a nested CASE statement or expression might be the way to go but I didn't make any real progress.

Any ideas to solve this problem would be appreciated.

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-01-25 : 09:23:07

I guess I can reply to my own question :-)
The solution below, which I received in another forum, seems to address my question and I'm including it here so it can help someone else -- notice the addition of the HAVING and UNION clauses:

SELECT h.JobNum,
(CASE WHEN MONTH(h.JobCompletionDate) = 1 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS JAN,
(CASE WHEN MONTH(h.JobCompletionDate) = 2 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS FEB,
...
FROM JobHead h
INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
AND l.ClockInTime<>0
GROUP BY h.JobNum
,h.JobCompletionDate

HAVING Count(*) > 1

UNION

SELECT h.JobNum,
(CASE WHEN MONTH(h.JobCompletionDate) = 1 THEN 1 ELSE 0 END) AS JAN,
(CASE WHEN MONTH(h.JobCompletionDate) = 2 THEN 1 ELSE 0 END) AS FEB,
...
FROM JobHead h
INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
AND l.ClockInTime<>0
GROUP BY h.JobNum
,h.JobCompletionDate
HAVING Count(*) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources