Script looking to prior records

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)

Script looking to prior records

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources