| 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 table
MPI- Links multiple patient accounts
Account # - Unique # per hospital visit
Admission date
Discharge Date
Clinical code
Here is an example of what I want to find.
Record 1
MPI- 00001234
Patient Account- 987456
Admission Date 1/1/2014
Discharge date 1/5/2014
Clinical Code- 618
Record 2
MPI- 00001234
Patient Account- 661245
Admission Date 1/20/2014
Discharge date 1/25/2014
Clinical Code- 618
Record 3
MPI- 00001234
Patient Account- 99185
Admission Date 2/10/2014
Discharge date 2/15/2014
Clinical Code- 300
I 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 T
WHERE
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 Method
SELECT
*
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 T
WHERE
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 A
INNER JOIN
Cte AS B
ON A.RowNum - 1 = B.RowNum
AND A.ClinicalCode = B.ClinicalCode
WHERE
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. |
|
|
|
|
|
|
Script looking to prior records