Author |
Topic |
choideyoung
Starting Member
10 Posts |
Posted - 2014-07-01 : 14:30:46
|
I am trying to write script that will return re-admission information for hospital system. I need to return a patient record # compared to the previous record if it meets some additional clinical codes.Here are fields in my tableMPI- Links multiple patient accountsAccount # - Unique # per hospital visitAdmission dateDischarge DateClinical codeHere is an example of what I want to find.Record 1MPI- 00001234Patient Account- 987456Admission Date 1/1/2014Discharge date 1/5/2014Clinical Code- 618Record 2 MPI- 00001234Patient Account- 661245Admission Date 1/20/2014Discharge date 1/25/2014Clinical Code- 618Record 3MPI- 00001234Patient Account- 99185Admission Date 2/10/2014Discharge date 2/15/2014Clinical Code- 300I need to be able to return the patient account where the date difference between the admission and previous discharge date is less than 30 days and both records have clinical code 618.In my example above account 2 would be returned but account 1 & 3 would not because it did not meet both criteria We are currently doing a lengthy workaround by extracting data so any assistance would be greatly appreciated. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-01 : 14:50:26
|
For future reference, it's much easier if you put your data in a consumable format (create table and insert statements). Here is one way to get the result you want:DECLARE @Foo TABLE (MPI INT, PatientAccountNum INT, AdmissionDate DATE, DischargeDate DATE, ClinicalCode INT)INSERT @Foo VALUES(1234, 987456, '1/1/2014', '1/5/2014', 618),(1234, 661245, '1/20/2014', '1/25/2014', 618),(1234, 99185, '2/10/2014', '2/15/2014', 300)SELECT *FROM ( SELECT *, LAG (DischargeDate, 1, NULL) OVER (PARTITION BY MPI ORDER BY AdmissionDate) AS PrevDate FROM @Foo WHERE ClinicalCode = 618 ) AS TWHERE DATEDIFF(DAY, PrevDate, AdmissionDate) < 30 |
|
|
choideyoung
Starting Member
10 Posts |
Posted - 2014-07-01 : 17:41:03
|
Thanks, this gets me close to what I need....but what about this scenario.INSERT @Foo VALUES(1234, 125895,'1/1/2014','1/9/2014', 618),(1234, 987456, '1/10/2014', '1/15/2014', 650),(1234, 661245, '1/20/2014', '1/25/2014', 618),(1234, 99185, '2/10/2014', '2/15/2014', 300)If I run the script provided it returns the account number of 661245….I would want the script to look to the previous record, I need it to be less than 30 days and I need the prior admission to also be the same code.In my scenario I would expect no accounts as they do not meet both criteria.Thanks again. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-02 : 11:51:30
|
[code]DECLARE @Foo TABLE (MPI INT, PatientAccountNum INT, AdmissionDate DATE, DischargeDate DATE, ClinicalCode INT)INSERT @Foo VALUES(1234, 987456, '1/1/2014', '1/5/2014', 618),(1234, 987456, '1/10/2014', '1/15/2014', 650),(1234, 661245, '1/20/2014', '1/25/2014', 618),(1234, 99185, '2/10/2014', '2/15/2014', 300)-- LAG MethodSELECT *FROM ( SELECT *, LAG(DischargeDate, 1, NULL) OVER (PARTITION BY MPI ORDER BY AdmissionDate) AS PrevDate, LAG(ClinicalCode, 1, NULL) OVER (PARTITION BY MPI ORDER BY AdmissionDate) AS PrevCode FROM @Foo ) AS TWHERE DATEDIFF(DAY, PrevDate, AdmissionDate) < 30 AND ClinicalCode = 618 AND ClinicalCode = PrevCode-- CTE Join Method;WITH Cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY MPI ORDER BY AdmissionDate) AS RowNum FROM @Foo)SELECT A.*FROM Cte AS AINNER JOIN Cte AS B ON A.RowNum - 1 = B.RowNum AND A.ClinicalCode = B.ClinicalCodeWHERE A.ClinicalCode = 618 AND DATEDIFF(DAY, B.DischargeDate, A.AdmissionDate) < 30[/code] |
|
|
choideyoung
Starting Member
10 Posts |
Posted - 2014-07-09 : 08:26:53
|
Thanks Lamprey...I think this gets me what I need. |
|
|
|
|
|