determine same contact or new contact

[andre_ficken]

I have a large set of patient appointment history sorted by patientId, appointment date/time start and appointment date/time end. I need to determine for each patient whether or not the appointments below to the same instance. This is the case when the start date/time of the appointment is >1800sec (30min) and <=259200sec (72hrs) after the previous appointments end date/time. How can I determine that the appointment belongs to the same instance? Any help or suggestion is appreciated.

[mfchmielowski]

Hi,

You've mentioned only about 4 columns (i assume that there's more of them). You can sort the dataset and use previous() to calculate timediff and then conditionally apply flag for the same instance records.

Code example looks like this:

AppointmentsTmp:
load * 
inline [
	id, patientId, startDate, endDate
	1, 1, 2021-05-11 12:00:00, 2021-05-11 12:30:00
	2, 1, 2021-05-11 13:40:00, 2021-05-11 13:00:00
	3, 1, 2021-05-17 12:40:00, 2021-05-17 13:00:00
	4, 2, 2021-05-17 12:40:00, 2021-05-17 13:00:00
];

NoConcatenate
Appointments:
load *
	, if(patientId <> Previous(patientId)
		, 'init'
		, if(timediff >= rangeStart and timediff <= rangeEnd
			, 'same'
			, 'other'
		)
	) as instance
;
load
	*
	, if(patientId = Previous(patientId)
		, num#(interval(startDate - Previous(endDate), 's'))
	) as timediff
	, num#(1800) as rangeStart
	, num#(259200) as rangeEnd
Resident AppointmentsTmp
order by
	patientId, startDate
;

drop table AppointmentsTmp;

drop fields rangeStart, rangeEnd from Appointments;

Result look like this:

Hope it helps.

P.S. What if the next appointment is 10 minutes (600 seconds) after ending of previous ?

[andre_ficken]

Thanks for your reply, I did get this far. But I need to get 1 step further. A new incident group starts with the first incident of a patient. Each following entry belongs to the same group as long as the patient is the same and the end time of the first incident in the group is less then 72 hours before the start time of each following incident.  Which means that when a patient has 10 incidents and the second, third and fourth incident have a start time  later then 30 minutes after the first incident and less then 72 hours after the first incident they will belong to the same group. When an incident is less than 30 minutes after the first incident it has to be discarded. When an incident starts more then 72 hours after the first incident in the group, that incident is the start of a new group. The end result that we need is that can see how many patients needed follow-up care after 30 minutes and within 24 hours or 72 hours of an incident. 

[andre_ficken · Accepted solution]

I ended up determining the base contacts and joining all contacts back to the base contacts. In that way I was able to determine whether the time differences for each contact compared to the base contact was within limits. After that eliminating all contacts that were outside the parameters set leaving only the contacts within limits. I am not sure whether it is the best method, but I got the correct answer.