SQL Help Get last encounter each year

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)

SQL Help Get last encounter each year

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2012-08-23 : 15:26:20

Aloh, I have a query that where I need to get the last encounter with a patient for a specific year. In other words I need the last encounter they had in 2005, 2006, 2007 etc. Not just the latest one to date. I can get the latest one no problem wondering how I get it to break for each year... Mahalo, Brew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 15:41:11

Something like this:

SELECT
	patient_id,
	YEAR(encounter_datetime) AS [Year],
	MAX(encounter_datetime) AS LatestEncounter
FROM
	PatientEncounters
GROUP BY
	patient_id,
	YEAR(encounter_datetime)

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-23 : 15:42:16

[code]
Create table #t1(Name varchar(10), LastDate datetime)
go

insert into #t1 values ('abc', '1/1/2011')
insert into #t1 values ('abc', '10/1/2011')
insert into #t1 values ('abc', '11/1/2010')
insert into #t1 values ('xyz', '1/1/2011')
insert into #t1 values ('xyz', '6/1/2007')
insert into #t1 values ('123', '1/1/2011')
insert into #t1 values ('123', '1/1/2010')
insert into #t1 values ('123', '1/1/2009')

SELECT Name, Max(LastDate)
FROM #t1
GROUP BY Name, YEAR(LastDate)
ORDER BY Name, YEAR(LastDate) DESC

drop table #t1
go
[/code]

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources