SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Script looking to prior records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

choideyoung
Starting Member

USA
9 Posts

Posted - 07/01/2014 :  14:30:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/01/2014 :  14:50:26  Show Profile  Reply with Quote
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

Edited by - Lamprey on 07/01/2014 14:50:52
Go to Top of Page

choideyoung
Starting Member

USA
9 Posts

Posted - 07/01/2014 :  17:41:03  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/02/2014 :  11:51:30  Show Profile  Reply with Quote
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
Go to Top of Page

choideyoung
Starting Member

USA
9 Posts

Posted - 07/09/2014 :  08:26:53  Show Profile  Reply with Quote
Thanks Lamprey...I think this gets me what I need.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000