get count of records for the MAX date

Hi, below is my sample data.

basically I want to get the COUNT of distinct item_id with MAX date. so in this case I need to get count as 2
as there are only 2 distinct itemids for 11th aug 2016. it should NOT count  xyz1             67259837      24/02/2016 00:00 eventhough this order number already exists for cust_id as the date is not the latest.
I only want the count of items for the max date.

can anyone please give me the sql for this ?

Many Thanks



 Many Thanks



 cust_id          item_id                  date              
 abc1             67259825      11/08/2016 00:00            
 abc1             67259837      11/08/2016 00:00            
 xyz1             67259825      24/02/2016 00:00            
 xyz1             67259837      24/02/2016 00:00            
 xyz1             67259825      24/02/2016 00:00
gvamsimbaAsked:
Who is Participating?
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.

Éric MoreauSenior .Net ConsultantCommented:
This would work;

DECLARE @test TABLE (
	cust_id  VARCHAR(10),
	item_id		VARCHAR(10),
	date		DATETIME
	)        

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

 SELECT * FROM @test

SELECT COUNT(DISTINCT item_id) 
FROM @Test
WHERE date = (SELECT MAX(date) FROM @Test)

Open in new window

0
gvamsimbaAuthor Commented:
Hi Eric, but where you are grouping by custid ?

Thanks
0
Éric MoreauSenior .Net ConsultantCommented:
apparently I missed that part from your question!

here you go;
DECLARE @test TABLE (
	cust_id  VARCHAR(10),
	item_id		VARCHAR(10),
	date		DATETIME
	)        

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

 SELECT * FROM @test

SELECT cust_id, COUNT(DISTINCT item_id) 
FROM @Test
WHERE date = (SELECT MAX(date) FROM @Test)
GROUP BY cust_id

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

gvamsimbaAuthor Commented:
Hi Eric, but your query is only checking the max date from the entire table but its not grouping by CUST_id.

can you please check ?

thanks
0
Éric MoreauSenior .Net ConsultantCommented:
so your question is not clear!

Can you show exactly your expected results?
0
Éric MoreauSenior .Net ConsultantCommented:
could it be:
DECLARE @test TABLE (
	cust_id  VARCHAR(10),
	item_id		VARCHAR(10),
	date		DATETIME
	)        

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

SELECT * FROM @test

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

Open in new window

0
gvamsimbaAuthor Commented:
sorry Eric my fault, Below is my correct sample data. for each cust_id, we need to check the MAX of date.

so in this case cust_id 1 has got max date as November 8th 2016 and cust_id 2 has got max date as 24th march 2016

can you please re-write your query ?

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

Many thanks
0
gvamsimbaAuthor Commented:
so we need to get the count of items per customer based on the Max date
0
Éric MoreauSenior .Net ConsultantCommented:
max date of what? customer? item?

What is your expected result from the data above?
0
gvamsimbaAuthor Commented:
eric there is a date PER customer. some customers got multiple dates so we need to select the MAX date and then count the number of items for that date PER customer.

expected output for above sample  is i need to get the item count as 2 for cust_id 1 and item count as 1 for cust_id 2 based on their count of ltems on the LATEST date PER CUSTOMER

Thanks
0
Éric MoreauSenior .Net ConsultantCommented:
what about this:
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

Open in new window

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
gvamsimbaAuthor Commented:
Excellent.
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.

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.