it’s difficult to explain, especially in english, sorry.
The first condition is right, that works the way you already told me.
The second condition is more complicated. I’d like to see every patient who did not die, but admitted into the hospital within the prior 365 days with the same diagnosis, but there are patients who where at least e.g. 6 time in hospital with the same diagnosis. But only between the second and third admission are less then 365 days. So when I choose the first or last date as reference, that patient will be excluded (in the example patient 3)
As an example:
|Patientnumber||Casenumber||Discharge date||Cause of discharge||Diagnosis|
|1||11||2014.03.13||treatment regularly ends||R57.9|
|1||12||2016.02.20||treatment regularly ends||R57.9|
|2||21||2013.01.11||treatment regularly ends||M54.5|
|2||22||2014.05.23||treatment regularly ends||I50.00|
|3||31||2011.04.24||treatment regularly ends||R57.9|
|3||32||2013.01.22||treatment regularly ends||R57.9|
|3||33||2013.03.28||treatment regularly ends||R57.9|
|3||34||2015.05.06||treatment regularly ends||R57.9|
I’ve tried it with the self-join Kettan suggested, so I can identify every patient. But then I’ve got the problem, that I need the date/case when the patient was admitted into the hospital within the prior 365 days with the same diagnosis for the first time, because I’d like to characterise the patients at this time (age and so on).