SQL case based on max date

Hi, below is my sample data. I want to add another column called rank based on the max date per cust_id. so the
first 2 records for abc1 should get rank as 1 and the next 3 records for cust_id for xyz1 should get rank as 2

my below case statement is working but the issue is since the  item_id 67259837 is being shared by both the cust_id's which is possible , I m getting rank as 1 for the 4th record. I need to get rank as 2 for this as the date is NOT the maximum date . can anyone please give me the correct case for this ?

Many Thanks

ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY cust_id,date DESC ) AS RANK

cust_id      item_id                  date                expectedrank
abc1      67259825      11/08/2016 00:00            1
abc1      67259837      11/08/2016 00:00            1
xyz1             67259825      24/02/2016 00:00            2
xyz1             67259837      24/02/2016 00:00            2
xyz1             67259825      24/02/2016 00:00            2
gvamsimbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I need to get rank as 2 for this as the date is NOT the maximum date . can anyone please give me the correct case for this ?
how you define "maximum date"? which one is the "maximum date"?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
see if this is what you want?

declare @yourTable table
(
	cust_id varchar(10),
	item_id varchar(10),
	date date
)
insert into @yourTable
values
('abc1','67259825','11 Aug 2016'),
('abc1','67259837','11 Aug 2016'),
('xyz1','67259825','24 Feb 2016'),
('xyz1','67259837','24 Feb 2016'),
('xyz1','67259825','24 Feb 2016')

;with cte as
(
	select a.cust_id, ROW_NUMBER() OVER (ORDER BY a.cust_id) AS expectedRank
	from (select cust_id from @yourTable group by cust_id) a
	group by a.cust_id
)
select a.*, b.expectedRank
from @yourTable a inner join cte b
on a.cust_id = b.cust_id

Open in new window


test it with more data to see expected output.
0
gvamsimbaAuthor Commented:
Hi Ryan MAX date is the latest date PER cust_id
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

gvamsimbaAuthor Commented:
DECLARE @test TABLE (
      cust_id            INT ,
      contractid      VARCHAR(10),
      item_id            VARCHAR(10),
      date            DATETIME
      )        

INSERT INTO @test ( cust_id, contractid, item_id, date )
VALUES ( 1, 'abc1', '67259825', '08/11/2016 00:00')            
     , ( 1, 'abc1', '67259837', '08/11/2016 00:00')            
     , ( 1, 'xyz1', '67259825', '02/24/2016 00:00')            
     , ( 1, 'xyz1', '67259837', '02/24/2016 00:00')            
     , ( 1, 'xyz1', '67259825', '02/24/2016 00:00')
     , ( 2, 'abc1', '67259828', '02/24/2016 00:00')
     , ( 2, 'abc1', '67259824', '03/24/2016 00:00')

SELECT * FROM @test

SELECT T1.cust_id, T1.date, COUNT(DISTINCT T1.item_id)
FROM @test AS T1
INNER JOIN (
      SELECT cust_id, MAX(date) AS Date FROM @test GROUP BY cust_id
) AS T2
ON T2.cust_id = T1.cust_id
AND T2.Date = T1.date
GROUP BY T1.cust_id, T1.date
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what you posted was grouped records, not really the one you mentioned in your question to produce 5 records, or i could have misunderstood your requirement.

anyway, glad you find a solution by yourself.
0
gvamsimbaAuthor Commented:
works as expected.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.