date and time calculations

  • 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.

     

     

  • 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

     

     

  • 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.

  • Nicely done... a couple of minor things you may want to change...

    1. If t2.ID>t1.ID then t2.ID cannot be equal to t1.ID.  Remove the t1.ID<>t2.ID criteria.
    2. In case the amount of time is not in ascending order, wrap the DATEDIFF calc in ABS to always return positive numbers.
    3. Of much lesser importance, you may want to sort from longest diff to shortest.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • My sincere thanks for your reply. This helped me very much !

     

    Jerry.

     

  • 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

    declare

    @times table (TaskID int, EmpID int, Dt datetime)

    insert

    @times

    select

    1, 1,'2006-06-29 10:00' union all

    select

    1, 2,'2006-06-29 11:00' union all

    select

    1, 3,'2006-06-29 21:00' union all

    select

    1, 4,'2006-06-29 17:00' union all

    select

    2, 4,'2006-01-12 12:00' union all

    select

    3, 2,'2006-06-29 15:00' union all

    select

    3, 4,'2006-06-29 10:00' union all

    select

    3, 3,'2006-06-29 17:00' union all

    select

    3, 5,'2006-06-29 19:00' union all

    select

    3, 6,'2006-06-29 10:00'

     

    select

    t1.TaskID,

    t2.QuickestTaskEnd,
    t1.EmpID,
    t1.Dt TaskEnd,

    datediff

    (mi, t2.QuickestTaskEnd, t1.Dt) DiffMin

    from

    @times t1

    join

    (select TaskID, min(Dt) QuickestTaskEnd from @times group by TaskID) t2

    on

    t1.TaskID = t2.TaskID

    order

    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