Calculate Number of Admits in Past 12 mos

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

Calculate Number of Admits in Past 12 mos

Author

Topic

Liro0917
Starting Member

7 Posts

Posted - 2013-08-05 : 13:09:53

I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months can anyone help Please!

Thank You!

Thanks!
Liro

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 13:15:11

My instict is to suggest a simple count - but I suspect that won't exactly give you what you need if you have more than one row for a given patient(?) Does each row represent a unique admission? If not, what is the logic to be used to weed out "non-admit" rows?

 SELECT COUNT(*) FROM YourTable WHERE AdmitDate >= DATEADD(yy,-1,GETDATE()) AND AdmitDate < GETDATE()

Liro0917
Starting Member

7 Posts

Posted - 2013-08-05 : 13:35:18

The information is comeing from an Admission Table. If I put a Distinct on the query that should remove any Dupes right?

Thanks!
Liro

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 13:39:31

quote:
Originally posted by Liro0917

The information is comeing from an Admission Table. If I put a Distinct on the query that should remove any Dupes right?

Thanks!
Liro

No, I am not sure that that will get you the right numbers. If two patients were admitted on the same date, you would be counting only one of those, which is probably not what you want.

Take a look at a few sample patients and see what kind of data is in the table. Is it a one row per patient type of data, or can there be multiple rows for a patient? If there are multiple rows per patient, and you are looking for patients who were admitted in the last 12 month period, then try this:

SELECT COUNT(DISTINCT PatientId) 
FROM YourTable 
WHERE AdmitDate >= DATEADD(yy,-1,GETDATE()) AND AdmitDate < GETDATE() ;

Even that may not be quite right if a patient was admitted, discharged and then readmitted within the 12 month period. This is why it is important to understand the data and what it represents.

Liro0917
Starting Member

7 Posts

Posted - 2013-08-05 : 16:16:13

Thanks this gave me a total Count however. What Im looking for is say Im a patient and I have been admitted to the hospital 4 times in twelve months it should show 4 different Admissions for my Member ID and so on for the next Member. I tried the Distinct but that won't work either If I don't use distinct it pulls up duplicate rows of member information like 4 identical lines. So I'm not sure what is causing that. Ill keep working at it but any other suggestions would be great. The data is Admission Data that represents a members admit to one of our facilities. I would think that Each time the Admission Date differs that is counted as an Admit. If they are the same then only count it once.

Thanks!
Liro

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 16:22:20

Give this a try. Here, I am assuming that a patient may have several rows in the table. Each row will have the id. A row may or may not have an admit date (depending on whether that row corresponds to an event that pertains to the patient admission or not).

SELECT COUNT(*) AS TotalAdmissions FROM
(
	SELECT DISTINCT patientId, AdmitDate
	FROM YourTable 
	WHERE AdmitDate >= DATEADD(yy,-1,GETDATE()) AND AdmitDate < GETDATE()
	AND AdmitDate IS NOT NULL
) s

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 16:24:29

If that doesn't give you what you want, run this query and see what you get. This is more to debug the problem than for your final output. Look at some sample patients returned by this query and see if the number of admissions match what you expect.

SELECT DISTINCT patientId, COUNT(DISTINCT AdmitDate) AS Admissions
FROM YourTable 
WHERE AdmitDate >= DATEADD(yy,-1,GETDATE()) AND AdmitDate < GETDATE()
AND AdmitDate IS NOT NULL
GROUP BY patientId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources