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?
 
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
 
Ryan ChongCommented:
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 ChongCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
gvamsimbaAuthor Commented:
Hi Ryan MAX date is the latest date PER cust_id
0
 
Ryan ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.