using the same aggregate function multiple times in 1 select stmt

• Ethan7

  SSC Rookie

  Points: 38

  More actions

  March 8, 2006 at 10:38 am

  #90715

  I have a statement that is essentially:

  Select

  , Person

  , MIN(VisitDate) AS First

  , MAX(VisitDate) AS Last

  , DATEDIFF(day, MAX(VisitDate), MIN(VisitDate)) AS Days_Between

  , CASE WHEN DATEDIFF(day, MAX(VisitDate), MIN(VisitDate)) >= 30 THEN 'Mark this person' ELSE NULL END

  FROM Visits

  GROUP BY Person

  I am curious if there is a better way to do this. Can I somehow reference the date calculated for 'First'? Does SQL Server run this MAX function twice? I am getting the result I expect, just wondering if there is a more efficient way to program this.

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  March 8, 2006 at 10:44 am

  #625478

  Yes it does.  You may want to try something like:

  Select

  , Person

  , First

  , Last

  , DATEDIFF(day, x.Last, x.First) AS Days_Between

  , CASE WHEN DATEDIFF(day, x.Last, x.First) >= 30 THEN 'Mark this person' ELSE NULL END

  From (Select

       , Person

       , MIN(VisitDate) AS First

       , MAX(VisitDate) AS Last

       FROM Visits

       GROUP BY Person) x

• Ethan7

  SSC Rookie

  Points: 38

  More actions

  March 8, 2006 at 11:02 am

  #625487

  Thanks!