June 29, 2006 at 1:12 pm
Hello,
I could use some help when it comes to a query dealing with employees and dates.
The table looks like this:
Emp_pk, Emp_date
There are four employees a,b,c,d for example and each one has an associated datetime stamp. The datetime stamp is a indication of how long the employee took to complete a task. What I need to determine is the time each employee took in relation to one another.
This will help determine where additional training or staff is needed.
How do you subtract one field from another in the same column ?
Thanks.
June 29, 2006 at 1:22 pm
DECLARE @t TABLE ( ID int, Dt datetime)
INSERT INTO @t VALUES (1,'2006-06-29 10:00')
INSERT INTO @t VALUES (2,'2006-06-29 11:00')
INSERT INTO @t VALUES (3,'2006-06-29 12:00')
INSERT INTO @t VALUES (4,'2006-06-29 13:00')
SELECT t1.ID, t1.Dt, t2.ID, t2.dt, datediff(mi,t1.dt, t2.dt) Diff
FROM @t t1 INNER JOIN @t t2 ON t1.ID <> t2.ID and t2.ID > t1.ID
ORDER BY t1.ID
June 29, 2006 at 1:46 pm
I just wanted to say how much I appreciate the solution to this. I was asked once why you'd ever want to join a table to itself, and this is a perfect example.
June 29, 2006 at 9:33 pm
Nicely done... a couple of minor things you may want to change...
Borrowing on the Junky's code...
DECLARE @t TABLE ( ID int, Dt datetime)
INSERT INTO @t VALUES (1,'2006-06-29 10:00')
INSERT INTO @t VALUES (2,'2006-06-29 11:00')
INSERT INTO @t VALUES (3,'2006-06-29 12:00')
INSERT INTO @t VALUES (4,'2006-06-29 13:00')
SELECT t1.ID AS t1ID,
t2.ID AS t2ID,
t1.Dt AS t1Dt,
t2.Dt AS t2Dt,
ABS(DATEDIFF(mi,t1.Dt,t2.Dt)) AS DiffMin
FROM @t t1,
@t t2
WHERE t2.ID>t1.ID
ORDER BY DiffMin DESC
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2006 at 7:52 am
My sincere thanks for your reply. This helped me very much !
Jerry.
June 30, 2006 at 8:46 am
Is the datetime an end time, or a duration (i.e. 0 duration = 1 jan 1900)? In the former case, you could use the code below (though you will have problems with tasks that span more than one day). In my humble opinion, the output of this query is clearer. If the times are durations, you can get rid of the join to the inline view (derived table), and use datediff(mi,0, t1.Dt) Diffmin
@times table (TaskID int, EmpID int, Dt datetime)
1, 1,'2006-06-29 10:00' union all
1, 2,'2006-06-29 11:00' union all
1, 3,'2006-06-29 21:00' union all
1, 4,'2006-06-29 17:00' union all
2, 4,'2006-01-12 12:00' union all
3, 2,'2006-06-29 15:00' union all
3, 4,'2006-06-29 10:00' union all
3, 3,'2006-06-29 17:00' union all
3, 5,'2006-06-29 19:00' union all
3, 6,'2006-06-29 10:00'
t1.TaskID,
(mi, t2.QuickestTaskEnd, t1.Dt) DiffMin
@times t1
(select TaskID, min(Dt) QuickestTaskEnd from @times group by TaskID) t2
t1.TaskID = t2.TaskID
by t1.TaskID, t1.Dt desc
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply