troubleshooting Question

how to find first record from dataset and group them by location using SQL

Avatar of moe57
moe57 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
18 Comments1 Solution390 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. Here are the criteria and the logic that has to be used when outputting the final data:
here is the logic that needs to be followed.
1) ER first visit
   a) if patient's first visit is ER, then display it and all other subsequent visits to ER like 2nd, 3rd etc
   b) if same patient goes to clinic after 90 days then display it
2) Clinic first visit
  a) if patient first visit is clinic then display it
  b) if same patient goes to clinic again do not display it
  c) if same patient goes to ER after 48 hours then display it.

Example:
- Mike first visit goes to ER on 1/1/2014 :- output this data
 - Mike goes again to ER on 1/3/2014 :- output this data
-  Mike goes to Clinic on 1/20/2014 :- do not output this data
- Mike goes to Clinic on 5/2/2014 : output this data
- David first visit goes to Clinic on 3/3/2014 : output this data
-  David goes back again to Clinic on 3/4/2014 : do not output this data
-  David goes to ER on 3/15/2014 : output this data

here is how my data looks like :
NAME    LOCATION           DATE
MIKE             ER                  1/1/2014
MIKE             ER                  1/3/2014
MIKE         CLINIC              1/20/2014
MIKE         CLINIC              5/2/2014
DAVID       CLINIC             3/3/2014
DAVID        CLINIC            3/4/2014
DAVID           ER                 3/15/2014

here is the desired results after the query is run:
NAME      LOCATION         DATE
    MIKE           ER               1/1/2014
    MIKE           ER               1/3/2014       
    MIKE      CLINIC             5/2/2014
    DAVID     CLINIC            3/3/2014
    DAVID      ER                   3/15/2014

here is the query i have started but not finished it
SET NOCOUNT ON
GO

    DECLARE @Init_DataSource TABLE
    (
         [NAME] VARCHAR(20)
        ,[DATE] DATE
        ,[LOCATION] VARCHAR(15)
    )

    INSERT INTO @Init_DataSource ([NAME], [DATE], [LOCATION])
    VALUES ('MIKE', '1-1-2014', 'ER')
          ,('MIKE', '1-3-2014', 'ER')
          ,('MIKE', '1-20-2014', 'CLINIC')
          ,('MIKE', '5-2-2014', 'CLINIC')
          ,('DAVID', '3-3-2014', 'CLINIC')
          ,('DAVID', '3-4-2014', 'CLINIC')
          ,('DAVID', '3-15-2014', 'ER')

SELECT * 
FROM @Init_DataSource
ORDER BY 1 DESC, 2 ASC
ASKER CERTIFIED SOLUTION
Simon
Principal Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 18 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 18 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