How to compare dates in 2 rows based on value in other column

• amar_kaur16

  SSC Eights!

  Points: 984

  More actions

  May 31, 2013 at 7:37 am

  #273349

  I have a table with studentId, decision and Finishdate

  Where studentid is the primary key and there could be none decision earlier and the aacept/deny later or vice-versa for a student.

  So, the decision could be accept/deny or None when there is no decision.

  I have to select the row of data when there is a decision (that is where decision is other than ’None’ )and finish date is most recent for a studentid.

  How do I do that?

  Thanks,

  blyzzard

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 31, 2013 at 8:56 am

  #1620122

  amar_kaur16 (5/31/2013)

  ---

  I have a table with studentId, decision and Finishdate

  Where studentid is the primary key and there could be none decision earlier and the aacept/deny later or vice-versa for a student.

  So, the decision could be accept/deny or None when there is no decision.

  I have to select the row of data when there is a decision (that is where decision is other than ’None’ )and finish date is most recent for a studentid.

  How do I do that?

  Thanks,

  blyzzard

  In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 31, 2013 at 9:02 am

  #1620126

  Total shot in the dark:

  select

  mt.studentId,

  max(mt.Finishdate) as MaxDate

  from

  dbo.myTable mt

  where

  exists(select 1 from dbo.myTable mt1 where mt.studentid = mt1.studentid and mt1.Decision <> 'None')

  group by

  mt.studentid;