T-SQL Help

Question

Post reply

T-SQL Help

Dave62

SSCertifiable

Points: 7459
More actions
April 9, 2013 at 1:54 pm

#276257

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

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

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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;

Dave62 SSCertifiable Points: 7459 More actions · Answer 3

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