How to create a select statement

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to create a select statement

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2014-01-30 : 13:13:27

I have the following table:

StartDate AccountNumber EndDate
11/1/2013 1000050005 9/1/2012
11/1/2013 1000050005 1/19/2013
11/1/2013 1000050005 1/20/2013
11/1/2013 1000050005 1/21/2013
11/1/2013 1000050005 1/22/2013
11/1/2013 1000050005 1/23/2013
11/1/2013 1000050005 11/1/2013
11/1/2013 1000050005 11/2/2013
11/1/2013 1000050005 11/3/2013
11/1/2013 1000050005 11/4/2013
11/1/2013 1000050005 11/5/2013
11/1/2013 1000050005 11/6/2013
11/1/2013 1000050005 11/7/2013
11/1/2013 1000050005 11/8/2013
11/1/2013 1000050005 11/9/2013
11/1/2013 1000050005 11/10/2013
11/1/2013 1000050005 11/11/2013
11/2/2013 2000100010 7/14/2011
11/2/2013 2000100010 7/3/2012
11/2/2013 2000100010 9/1/2012
11/2/2013 2000100010 12/2/2012
11/2/2013 2000100010 1/1/2013
11/2/2013 2000100010 4/2/2013
11/2/2013 2000100010 5/2/2013
11/2/2013 2000100010 10/2/2013
11/2/2013 2000100010 11/1/2013
11/2/2013 2000100010 11/2/2013
11/2/2013 2000100010 11/3/2013
11/2/2013 2000100010 1/1/2014
11/2/2013 2000100010 1/2/2014
11/2/2013 2000100010 1/3/2014
11/2/2013 2000100010 1/4/2014
11/2/2013 2000100010 1/5/2014
11/2/2013 2000100010 1/6/2014

I want to create a select statement that will return the following results:
StartDate AccountNumber EndDate
11/1/2013 1000050005 11/11/2013
11/2/2013 2000100010 1/6/2014

I want to return the account number with the maximum date for Endate by startdate.

Can this be done?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-30 : 13:37:22

I think this should do what you want:

SELECT
	StartDate,
	AccountNumber,
	MAX(EndDate) AS EndDate
FROM
	TableName
GROUP BY
	AccountNumber
	StartDate

mlawton
Starting Member

35 Posts

Posted - 2014-01-30 : 13:55:30

Thanks!!! This is perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources