SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

308 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
52317 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000