Post reply

GET 1 RECORD WHERE STARTDATES ARE IDENTICAL

  • 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'

  • 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!

  • 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/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply