The trick for my challenge is to find where the patient was FIRST seen, meaning was the patient seen at the clinic first or at the emergency room, once we find out that then I need to group them and output the data.
The locations are categorized into these 2 groups: ER group and Clinic group
• ER Group Locations = ER, OBS and IP
• Clinic Group Locations = Clinic and OP
Here is the logic that needs to be followed.
1) ER GROUP LOCATION (first visit)
a) display only the first visit if patient's makes a visit to any of the ER group locations within the first 48, do not display more than one visit if same patient goes to the any of the ER group location within the first 48 hours except the first one,
b) display any subsequent visits to any of the ER group location after 48 hours
c) if same patient goes to the any of the Clinic group location after 90 days, then display
2) CLINIC GROUP LOCATION (first visit)
a) display if patent makes a first visit to any of the clinic group locations
b) if same patient goes to any of the clinic locations again do not display it
c) if same patient goes to any of the ER group location after 48 hours then display it
here is how my data look like:
here is the desired results after the query is run
i hope i explained little bit better this time. Please note that the data for this query is huge. Thanks