GET 1 RECORD WHERE STARTDATES ARE IDENTICAL

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  February 10, 2015 at 8:37 am

  #294604

  CREATE TABLE TEMPS(MEMBERNBR VARCHAR(11), STARTDATE DATETIME, ENDDATE DATETIME)

  INSERT INTO TEMPS(MEMBERNBR, STARTDATE, ENDDATE) VALUES('12345678901', '01/01/2015', '01'01'2015')

  INSERT INTO TEMPS(MEMBERNBR, STARTDATE, ENDDATE) VALUES('12345678901', '01/01/2015', '12'31'2099')

  I need to retrieve only the record where the STARTDATE is '01/01/2015' and the ENDDATE is '12/31/2099'

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 10, 2015 at 8:40 am

  #1776651

  must be missing something int he question... you can limit the results by using top, but top should have an order by; do you want the first member, last member, or it doesn't matter?

  SELECT TOP 1 *

  FROM TEMPS

  WHERE STARTDATE ='01/01/2015'

  and ENDDATE ='12/31/2099'

  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!

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  February 10, 2015 at 8:49 am

  #1776653

  Lowell (2/10/2015)

  ---

  must be missing something in the question...

  Same thought crossed my mind.

  With the given information, I am guessing that the ending date is just a magic date far in the future. If you have only one of those per membernbr, then either solution will work fine.

  This is what I came up with:

  ;with cte as

  (

  select membernbr, startdate, enddate,

  Row_Number() over (partition by membernbr order by enddate desc) RowNum

  from #TEMPS

  )

  select * from cte

  where RowNum = 1

  If you have multiple rows with the same ending date and same membernbr, then you will need to devise a tie-breaker.

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/