Need data from two different records

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

General SQL Server Forums

New to SQL Server Programming

Need data from two different records

Author

Topic

FranklinM
Starting Member

2 Posts

Posted - 2012-09-12 : 14:21:39

I am trying to use SQL to display data from a date field with a begin date (the oldest date) from one record and another date field with an end date (the newest date) in a different record.
These records contain the same users account name with multiple begin and end dates.
I can't seem to be able to select two differnet records to collect the two different data fields.

Thanks,
Matthew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:45:39

[code]
SELECT accountname,MIN(startdate) as Startdt,MAX(EndDate) AS Enddt
FROM table
GROUP BY accountname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 14:49:39

If you are looking to get other fields other than the accountname and the min and max dates, you can do something like shown below:

;WITH cte as
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY username, accountname ORDER BY theDateColumn ASC) AS Oldest,
		ROW_NUMBER() OVER (PARTITION BY username, accountname ORDER BY theDateColumn DESC) AS Newest
	FROM
		YourTable
)
SELECT
	a.*,
	b.*
FROM
	cte a
	INNER JOIN cte b ON a.username = b.username AND a.accountname = b.accountname
WHERE
	a.Oldest = 1 
	AND b.Newest = 1;

FranklinM
Starting Member

2 Posts

Posted - 2012-09-12 : 19:24:58

Thanks for the help. I will give these recommendations a try.

Thanks,
Matthew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:21:55

let us know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources