If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex Count function with a condition
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! |
#2
|
|||
|
|||
Complex Count function with a condition
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")); -- Build a little, test a little. "AlwaysLearing" wrote: 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! . |
Thread Tools | |
Display Modes | |
|
|