troubleshooting Question

find first record and group them by locations in sql

Avatar of moe57
moe57 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
13 Comments1 Solution371 ViewsLast Modified:
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:
Capture.PNG  

here is the desired results after the query is run
Capture2.PNG
i hope i explained little bit better this time.  Please note that the data for this query is huge.  Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros