getting the date difference from within 1 table

• bennyt

  SSC Rookie

  Points: 48

  More actions

  July 25, 2009 at 5:07 am

  #203095

  hi

  i have 1 table and i want to find out the number of days break each team has between games. for example below car has a 9 day break between 3/26/2009 and 4/4/2009. i'm not too sure how you'd go about it.

  my game table includes

  game_id

  date

  time

  round

  home

  away

  venue_id

  the type of data stored

  game_iddatetimeroundhomeawayvenue_id

  13/26/200919:40:001riccarmcg

  23/27/200919:40:001hawgeemcg

  33/28/200914:10:001colademcg

  43/28/200918:10:001briwcegabba

  63/28/200919:10:001stksyddoc

  73/29/200913:10:001melkanmcg

  104/3/200919:40:002adestkfp

  114/4/200914:10:002geerickp

  124/4/200914:10:002colmelmcg

  134/4/200919:10:002carbridoc

  the desired outcome would be something like below

  6 days7 days8 days

  ade111

  bri102

  car210

  thanks

  ben

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 25, 2009 at 5:37 am

  #1030133

  something like this should get you started; you have to join the table against itself to get the date differences.

  SELECT game_id,DATEDIFF(day,MinSet.date,MaxSet.date)

  From (select home as x, min(date) as date from YourTable group by game_id) MinSet

  INNER JOIN (select away as x, max(date) as date from YourTable group by game_id) MaxSet

  On MinSet.x = On MaxSet.x

  if you had provided actual CREATE TABLE and INSERT into statements, i could have helped better.

  bennyt (7/25/2009)

  ---

  hi

  i have 1 table and i want to find out the number of days break each team has between games. for example below car has a 9 day break between 3/26/2009 and 4/4/2009. i'm not too sure how you'd go about it.

  my game table includes

  game_id

  date

  time

  round

  home

  away

  venue_id

  the type of data stored

  game_iddatetimeroundhomeawayvenue_id

  13/26/200919:40:001riccarmcg

  23/27/200919:40:001hawgeemcg

  33/28/200914:10:001colademcg

  43/28/200918:10:001briwcegabba

  63/28/200919:10:001stksyddoc

  73/29/200913:10:001melkanmcg

  104/3/200919:40:002adestkfp

  114/4/200914:10:002geerickp

  124/4/200914:10:002colmelmcg

  134/4/200919:10:002carbridoc

  the desired outcome would be something like below

  6 days7 days8 days

  ade111

  bri102

  car210

  thanks

  ben

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!