Date Calculations

• Allen B

  SSC-Addicted

  Points: 473

  More actions

  May 15, 2007 at 7:21 am

  #195732

  Hi All,

  I have two tables, Header and Detail. Header has a status field with "open" or "closed".  The Detail table has multiple rows for each Header row, in the Detail rows is a date field called transDate.  I'm trying to get the min and max date from each row and find the number of days difference, then get an average for the number of days different. Min and Max just gets two dates as I expect.  How can you get the min and max for each parent ID then calculate the average for them all?  Can this be done in one statement or do I need to create a temp table?

  Sample Data

  Header

  ID Status

  1   open

  2   closed

  3   closed

  4   open

  Detail

  ID transDate

  1   2007-05-01 17:52:33.000

  1   2007-05-05 11:55:31.000

  1   2007-05-14 12:42:23.000

  2   2007-04-15 16:33:33.000

  3   2007-04-14 13:54:35.000

  3   2007-04-28 12:32:43.000

  3   2007-04-14 11:42:33.000

  3   2007-05-01 11:57:23.000

  3   2007-05-14 17:32:31.000

  4   2007-05-12 19:22:23.000

  4   2007-05-14 11:51:33.000

  Expected Results

  intermediate results

  ID Min Date                             Max Date 

  2   2007-04-15 16:33:33.000  2007-04-15 16:33:33.000

  3   2007-04-14 13:54:35.000  2007-05-14 17:32:31.000

  Final Result

  (0+30)/2=15

  average days: 15

  Thanks

  Thanks

• Allen B

  SSC-Addicted

  Points: 473

  More actions

  May 15, 2007 at 7:32 am

  #706479

  Never mind, I figured it out.

  select AVG(days)

  from

  (select datediff(dd, min(sc.transDate), max(sc.transDate)) as days

   from  detail sc

  inner join header sm on sm.ID = sc.ID where sm.status = 'CLOSED'

  group by sm.ID) tblTemp

   

  Thanks

  Thanks