Compare patient visit times

Question

Hi,

I am writing this again a different way

Senario

Show me all records where the MRN(PatientId) has arrived in Hosptial with a PrimaryDiagnosis=(FirstVisit), if the patient comes back again on another date with the same PrimaryDiagnosis within 7 days = (WithIn7days), if the same patient comes back again on a different date with the same PrimaryDiagnosis greater than 7days then = (After7days) and so on.

I have tried different ways to do this but i am not quite there.

Any help would be appreciated, i have been using a case statement with OuterApply

 

Row 5 and 7 in my output table (below)are the same patient they have the same PrimaryDiagnosis on different dates and there fore one should be FirstVisit and the other After7days, both are FirstVisit.

Table at the following link

http://social.msdn.microsoft.com/Forums/getfile/87910

---

David

Answer 1

Hi David,

It would be great if you could post the desired results for the same..

---

Rajkumar

Answer 2

David , just guessing

SELECT *,(SELECT MIN(dt) FROM tbl T WHERE t.id=tbl.id and....) AS First  ,

               (SELECT MIN(dt) FROM tbl T WHERE t.id=tbl.id and....) AS after7days

FROM tbl

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

HI,

Your statement: It would be great if you could post the desired results for the same..

Do you mean the following

http://social.msdn.microsoft.com/Forums/getfile/87910

This link is showing the output, the problem is that my script is producing a error

So on Row 5 and 7 are the same Patient, but they both show a first visit, when they should show FirstVist and row 7 should show After7Days

I have tried posting this jpeg into the form but it will not accept it, so i have to paste the link

---

David

Answer 4

Hello Kiwi,

What do you want to expect to see if the patient comes

on 1st of month with Pri.Dia. "S06",

on 2st of month with same pri.diagnosis

on 20th of month with same diagnosis and

on 21th of month with same diagnosis

I'ld prefer to see

First Visit

WithIn 7 Days

First Visit

WithIn 7 Days

Or do you want

First Visit

WithIn 7 Days

After 7 Days

WithIn 7 Days

 

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 5

Thanks David, Can you post the source data..? I thought the first one u posted was source.

---

Rajkumar

Answer 6

Hi Thanks to all for the help Ok First Visit

WithIn 7 Days

After 7 Days

Logic: Patient 1 visits is diagnosed given code S36. = Firstvisit(they have never had this diagnosis before)

Patient 1 visits is diagnosed given code S36. = WithIn7Days(2 visit)

Patient 1 visits is diagnosed given Code S36. = After7Days(3-4-5 visit)

Patient 1 visits is diagnosed given Code P23. = FirstVisit(New Diagnosis)

---

David

Answer 7

Hi Kiwi,

Can you check the following SQL CTE Query if it helps

with visit as (
	select 
		URNumber,
		PrimaryDiagnosis,
		min(ArrivalDateTime) over (partition by URNumber, PrimaryDiagnosis) FirstVisitDate,
		row_number() over (partition by URNumber, PrimaryDiagnosis order by ArrivalDateTime desc) visit_number,
		ArrivalDateTime
	from PatientVisit
)
select 
	*,
	case 
		when visit_number = 1 then 'First Visit' 
		else 
			case when datediff(dd, FirstVisitDate, ArrivalDateTime) < 7 then 'Within 7 Days'
			else 'After 7 Days'
		end
	end
from visit

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 8

Hi eralper

this looks like the deal,

I will test it tomorrow, and let you know how i go,

Thanks to all for the help.

Back sometime tomorrow

:)

---

David

Answer 9

Hi eralper

I have just returned from holiday,

thanks for this script, works well

Sorry for the late reply

:)

---

David

Hi,

I am editing the script that eralper supplied as i found a problem with the output, in the follwing i have changed

the

row_number() over (partition by URNumber, PrimaryDiagnosis order by ArrivalDateTime desc)

row_number() over (partition by URNumber, PrimaryDiagnosis order by ArrivalDateTime )

Removing the desc order.

I now get the correct result

with visit as (
select
URNumber,
PrimaryDiagnosis,
min(ArrivalDateTime) over (partition by URNumber, PrimaryDiagnosis) FirstVisitDate,
row_number() over (partition by URNumber, PrimaryDiagnosis order by ArrivalDateTime desc) visit_number,
ArrivalDateTime
from PatientVisit
)
select
*,
case
when visit_number = 1 then 'First Visit'
else
case when datediff(dd, FirstVisitDate, ArrivalDateTime) < 7 then 'Within 7 Days'
else 'After 7 Days'
end
end
from visit

---