We help IT Professionals succeed at work.
Get Started

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

383 Views
Last Modified: 2014-12-04
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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window

Comment
Watch Question
Principal Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 18 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE