Most frequent occurance - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Most frequent occurance

Author

Topic

acutshall
Starting Member

1 Post

Posted - 2007-06-18 : 14:15:34

I'm working with multiple variations of patient data. A patient may have multiple registrations each with a different patient number (but all tied together with the same patient ID). What I'm trying to determine is the most frequent date of birth (DOB) for any given patient. In case of a draw, I will take the one with the latest activity date. If someone could help me construct a SQL query to determine this on a given set of patients, I would be most appreciative because I'm already behind the proverbial

.

The table in question would look something like this:

tbPatientVariations
-------------------
PatientID int
PatientNbr varchar(16)
PatientName varchar(64)
PatientDOB datetime

Thanks for all of your help,

Aaron N. Cutshall
Business Systems Analyst

Indiana Health Information Exchange
846 N. Senate Av., Suite 300
Indianapolis, IN 46202-4121

Direct: 317-644-1742
Main: 317-644-1750
Fax: 317-644-1751

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-18 : 14:21:45

select patientid, patientdob
from tbPatientVariations
group by patientid, patientdob
order by patientid

since there is no column for activitydate, this is the nearest I can provide.

Peter Larsson
Helsingborg, Sweden

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-18 : 14:57:58

Aaron,

Expanding on Peso's query, this one gives you the frequency of each DOB for a patient:

select patientid, patientdob,
count(*) as Frequency from tbPatientVariations
group by patientid, patientdob
order by patientid

After that you can use Row_number or rank over to get the most frequent row for each patientid.

To give you the exact solution, we need some DDL table creation statements and insert statements with the data.( Also for the Latest acitivity date's table)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources