date and time calculations

• Jerry Tienter-162701

  Say Hey Kid

  Points: 694

  More actions

  June 29, 2006 at 1:12 pm

  #114155

  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.

   

   

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  June 29, 2006 at 1:22 pm

  #646824

  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

   

   

• Julie Hargraves

  SSCarpal Tunnel

  Points: 4457

  More actions

  June 29, 2006 at 1:46 pm

  #646834

  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.

• Jeff Moden

  SSC Guru

  Points: 1004407

  More actions

  June 29, 2006 at 9:33 pm

  #646879

  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)
  

• Jerry Tienter-162701

  Say Hey Kid

  Points: 694

  More actions

  June 30, 2006 at 7:52 am

  #646967

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

   

  Jerry.

   

• stax68

  SSChampion

  Points: 11711

  More actions

  June 30, 2006 at 8:46 am

  #646991

  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