T-SQL Help

  • This seems like a very simple question but for some reason the answer is eluding me.

    Given the following table data (Customers) how do I select all the records excluding the duplicates with an earlier Actual Date?

    Next CustId Customer Scheduled Date Actual Date Last

    2013 101 Cust1 2011-04-08 2011-04-08 2011

    2013 202 Cust2 2011-03-31 2011-03-31 2011

    2013 202 Cust2 2010-12-02 2010-12-02 2011

    2013 303 Cust3 2011-04-13 2011-04-13 2011

    2013 404 Cust4 2010-12-29 2010-12-29 2012

    2013 505 Cust5 2011-06-20 2011-06-20 2011

    2013 505 Cust5 2011-01-25 2011-01-25 2011

    2013 606 Cust6 2010-10-07 2010-10-07 2011

    2013 707 Cust7 2010-07-20 2010-07-20 2011

    Would like to get:

    Next CustId Customer Scheduled Date Actual Date Last

    2013 101 Cust1 2011-04-08 2011-04-08 2011

    2013 202 Cust2 2011-03-31 2011-03-31 2011

    2013 303 Cust3 2011-04-13 2011-04-13 2011

    2013 404 Cust4 2010-12-29 2010-12-29 2012

    2013 505 Cust5 2011-06-20 2011-06-20 2011

    2013 606 Cust6 2010-10-07 2010-10-07 2011

    2013 707 Cust7 2010-07-20 2010-07-20 2011

    Thanks,

    Dave

  • i'd consider using row_number() fo5r this one, i htink:

    SELECT * FROM

    (

    SELECT ROW_NUMBER() OVER( PARTITION BY CustId ORDER BY[Actual Date] DESC) AS RW,

    [Next],

    CustId,

    Customer,

    [Scheduled Date],

    [Actual Date],

    [Last]

    FROM [Customer]

    ) MyAlias

    WHERE RW = 1

    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!

  • This?

    declare @TestData table(

    NextYear int,

    CustId int,

    Customer varchar(5),

    ScheduledDate date,

    ActualDate date,

    LastYear int);

    insert into @TestData

    values

    (2013,101,'Cust1','2011-04-08','2011-04-08',2011),

    (2013,202,'Cust2','2011-03-31','2011-03-31',2011),

    (2013,202,'Cust2','2010-12-02','2010-12-02',2011),

    (2013,303,'Cust3','2011-04-13','2011-04-13',2011),

    (2013,404,'Cust4','2010-12-29','2010-12-29',2012),

    (2013,505,'Cust5','2011-06-20','2011-06-20',2011),

    (2013,505,'Cust5','2011-01-25','2011-01-25',2011),

    (2013,606,'Cust6','2010-10-07','2010-10-07',2011),

    (2013,707,'Cust7','2010-07-20','2010-07-20',2011);

    select * from @TestData;

    with BaseData as (

    select

    NextYear,

    CustId,

    Customer,

    ScheduledDate,

    ActualDate,

    LastYear,

    rn = row_number() over (partition by CustId order by ScheduledDate desc)

    from

    @TestData

    )

    select

    NextYear,

    CustId,

    Customer,

    ScheduledDate,

    ActualDate,

    LastYear

    from

    BaseData

    where

    rn = 1;

  • Thank you so much Lowell and Lynn. I'll give both of these a try and see if there is any difference in the query plan.

    Enjoy!

    Dave

Viewing 4 posts - 1 through 4 (of 4 total)

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