Count of patient re-referrals - 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 2012 Forums

Transact-SQL (2012)

Count of patient re-referrals

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2013-11-25 : 12:44:25

If I have a table of referrals for patients like the one below how would I do a count of all the referrals that are re-referrals?
I will be declaring two date variables (@StartDate and @EndDate) and:-

the discharge date for the patient's first referral must be between @StartDate and @EndDate

for any re-referrals the referral date and just one of any contact dates must be between @StartDate and @EndDate

So this would be a count of re-referrals and not a count of the patients themselves, so a patient can have multiple referrals but only some of those will be re-referrals.
For instance if I set my two date variables to cover all of the years 2009 and 2010 then in the table below there are 3 re-referrals that meet the above criteria (patients 27,72,76).
I hope this makes sense!
Many thanks.


PATIENT	REFERRAL_DATE	CONTACT_DATE	DISCHARGE_DATE
3	04/08/2009	02/08/2009	01/02/2010
27	18/11/2009	19/11/2009	17/12/2009
27	31/12/2009	01/02/2010	02/02/2010
27	04/05/2010	05/05/2010	06/05/2010
51	18/01/2010	18/01/2010	18/01/2010
72	10/04/2010	20/04/2010	25/04/2010
72	05/05/2010	06/05/2010	17/05/2010
75	10/02/2010	01/03/2010	05/04/2010
76	25/01/2010	01/02/2010	15/02/2010
76	17/02/2010	15/04/2010	25/04/2010

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-11-26 : 03:09:56

How do you work out if it is a re-referral as I would assume from your data supplied that you have 4 ( 2x27,1x72,1x76)?

Also, is Discharge date always populated? I am going to assume it is for now.


SELECT Patient, COUNT(*)
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PATIENT) rownum
, Patient
FROM
 PatientsTable
WHERE
 Discharge_date >= @StartDate AND Dischanrge_date <= @EndDate
) tbla
WHERE RowNum > 1
GROUP BY Patient

I'll leave you to add the logic for the other date criteria.

stamford
Starting Member

47 Posts

Posted - 2013-11-26 : 04:40:52

quote:
Originally posted by RickD

How do you work out if it is a re-referral as I would assume from your data supplied that you have 4 ( 2x27,1x72,1x76)?

Also, is Discharge date always populated? I am going to assume it is for now.

SELECT Patient, COUNT(*)
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PATIENT) rownum
, Patient
FROM
PatientsTable
WHERE
Discharge_date >= @StartDate AND Dischanrge_date <= @EndDate
) tbla
WHERE RowNum > 1
GROUP BY Patient

I'll leave you to add the logic for the other date criteria.

Thanks for this. The date of discharge is not always populated, and does the function ROW_NUMBER() need an ORDER BY clause?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-11-26 : 04:43:18

Yeah, it does, ORDER BY Patient as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources