1 of 1 people found this helpful
You'll need help with string manipulation.
functions such as FIND, LEN, MATCH, LEFT, RIGHT, MID, COUNT etc.
This is a great resource
The simplist in your exmaple is simply
left([diagnosis],1) to always get the first letter - this assumes they are always the same length...
more to follow on the rest...
this is a way to count the B, C etc.
you would need to count them apart...
instead, you could use the CONTAINS function... same outcome.
what maybe easier is to split the diagnosis string on import (using the ,) then you have them already split for ease of manipulation.
fun problem and quite a few ways to solve, just need to work out whats most efficient
Maybe my description was not very clear.
I want to know which are the patient's diagnosis (distribution) that have a specified principal diagnosis (precondition).
In reference to the above example, only patients 1,2,3 contribute to result and the distribution of diagnosis are: 1 patient with B diagnosis and 2 patient with C diagnosis.
The number of different types diagnosis can be very big.
select diagnosis,count(distinct patient)
where diagnosis<>'A' and patient in
select patient from patientdiagnosis_table where diagnosis='A'
Group by diagnosis
I hope that's more explicit.