Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 t-sql max date
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 02/28/2014 :  12:56:01  Show Profile  Reply with Quote
In t-sql 2012, I have a table that I need to join to itself server items based upon customer number, cust_date, and attribute id.
The attrribute id values are 53, 54, and 55. There are the same attribute ids that can occur lots of times during the year so the cust_date can change.
I need to join rows in the table to itself several times where the cust_date is the same and the most current date.

Thus can you show me how to join the table to itself with selecting the maximum cust_date and the for attribute value = 53, 54, and 55?

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/28/2014 :  13:08:42  Show Profile  Reply with Quote
I might be able to help better if you provide sample data and expected output. Depending on the nature of your data you might be able to use the Row_Number function or you might want to use a join to a derived table. Maybe this will help get you going:
SELECT 
	*
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY CustomerNumber ORDER BY cust_date DESC) AS RowNum
		FROM
			cust_data
	) AS T
WHERE
	RowNum = 1


-- OR

SELECT
	*
FROM
	cust_data
INNER JOIN
	(
		SELECT CustomerNumber, MAX(cust_date) AS cust_date
		FROM cust_data
		GROUP BY CustomerNumber
	) AS T
	ON cust_data.CustomerNumber = T.CustomerNumber
	AND cust_data.cust_date = T.cust_date
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/01/2014 :  10:55:35  Show Profile  Reply with Quote
quote:
Originally posted by jassie

In t-sql 2012, I have a table that I need to join to itself server items based upon customer number, cust_date, and attribute id.
The attrribute id values are 53, 54, and 55. There are the same attribute ids that can occur lots of times during the year so the cust_date can change.
I need to join rows in the table to itself several times where the cust_date is the same and the most current date.

Thus can you show me how to join the table to itself with selecting the maximum cust_date and the for attribute value = 53, 54, and 55?


sounds like this to me

SELECT CustomerNumber,
MAX(CASE WHEN AttributeID = 53 THEN Cust_Date END) AS [LatestDate53],
MAX(CASE WHEN AttributeID = 54 THEN Cust_Date END) AS [LatestDate54],
MAX(CASE WHEN AttributeID = 55 THEN Cust_Date END) AS [LatestDate55]
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY CustomerNumber,AttributeID ORDER BY cust_date DESC) AS RowNum
		FROM
			cust_data
	) AS T
WHERE
	RowNum = 1
GROUP BY CustomerNumber


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.13 seconds. Powered By: Snitz Forums 2000