Complex Count function with a condition

  • Thread starter Thread starter AlwaysLearing
  • Start date Start date
A

AlwaysLearing

Hello,
I have a table named, tbl_pt_records as shown below.

ID AdmitDate EndDate AdType Place Comments
NH
1*01 4/15/2009 4/27/2009 InitialAdmit Hospital Discharge to
Home ABC
1*01 5/1/2009 5/3/2009 Readmit Hospital Discharge to
Nursing ABC
1*01 5/3/2009 5/5/2009 InitialAdmit Nursing Still at
Nursing ABC
1*01 5/5/2009 5/15/2009 Readmit Hospital Discharge to Nursing
ABC
2*05 9/1/2009 9/5/2009 InitialAdmit Hospital Discharge to
Nursing ABC


I need to count all cases where AdType = "Readmit" with the condition
that person was admitted from the Nursing Home (line 4 is the correct
scenario where the person was in the NH from 5/3 - 5/5 and on 5/5 was
admitted to the Hospital). I want to exclude line 2 since the person
was discharge to Home and probably stayed home from 4/27 to 5/1.

From the above scenario, AdType ="Readmit would be equal to 1.

Is this possible to count? thanks!
 
Try this --
SELECT Count(tbl_pt_records.ID) AS CountOfID
FROM tbl_pt_records INNER JOIN tbl_pt_records AS tbl_pt_records_1 ON
tbl_pt_records.ID = tbl_pt_records_1.ID
WHERE (((tbl_pt_records.AdmitDate)=[tbl_pt_records_1].[EndDate] Or
(tbl_pt_records.AdmitDate)=([tbl_pt_records_1].[EndDate])+1) AND
((tbl_pt_records.AdType)="Readmit") AND ((tbl_pt_records_1.Place)="Nursing")
AND ((tbl_pt_records.Place)="Hospital"));
 
Back
Top