find first record and group them by locations in sql

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
moe57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I think this will do. However, I could not test, as you have not provided the data in text format, so that it can be inserted into SQL Fiddle
;with grp as (
SELECT CASE WHEN Location IN ('ER', 'OBS', IP) THEN 'ER' WHEN LOCATION IN('CLINIC', 'OP') THEN 'CLINIC' END GRP, *
FROM TABLE1),
grprn AS(
SELECT ROW_NUMBER OVER(PARTITION BY [Name] ORDER BY [DATE]) rn, * FROM grp),
ERs AS(
SELECT * FROM grprn WHERE GRP = 'ER'),
CLs AS(
SELECT * FROM grprn WHERE GRP = 'CLINIC')
SELECT [Name], [Location], [DATE] FROM ERs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] FROM ERs a WHERE RN > 1 AND NOT EXIST(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = a.[Name] AND [Date] < DateAdd(hh, 48, a.[Date]))
UNION ALL
SELECT [Name], [Location], [DATE] FROM CLs b WHERE RN > 1 AND NOT EXIST(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = b.[Name] AND [Date] < DateAdd(d, 90, b.[Date]))
UNION ALL
SELECT [Name], [Location], [DATE] FROM CLs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] FROM ERs c WHERE RN > 1 AND NOT EXIST(SELECT 1 FROM CLs WHERE RN = 1 AND [Name] = c.[Name] AND [Date] < DateAdd(hh, 48, c.[Date]))
ORDER BY 1, 3

Open in new window

0
moe57Author Commented:
sorry, here is the data to test it
NAME	LOCATION	DATE
MIKE	ER	1/1/2014
MIKE	ER	1/2/2014
MIKE	OBS	1/20/2014
MIKE	IP	1/28/2014
MIKE	OBS	3/15/2014
MIKE	CLINIC	1/20/2014
MIKE	OP	6/20/2014
MIKE	CLINIC	6/15/2014
DAVID	CLINIC	3/3/2014
DAVID	CLINIC	3/4/2014
DAVID	OP	3/5/2014
DAVID	ER	3/15/2014
DAVID	OBS	3/20/2014
TOM	IP	5/5/2014
TOM	OBS	5/6/2014
TOM	ER	5/8/2014
TOM	OP	9/15/2014

Open in new window

i will test it later .  thanks
0
moe57Author Commented:
i just quickly looked at it and i see that you are grouping locations in the final output
SELECT CASE WHEN Location IN ('ER', 'OBS', IP) THEN 'ER' WHEN LOCATION IN('CLINIC', 'OP') THEN 'CLINIC' END GRP, *
FROM TABLE1),

Open in new window

but i would like to see in the final output where each patient went like if Mike went to OBS or ER or Tom went to Clinic or OP so it is important to know in the final output where every patient went.  Thanks
NAME	LOCATION	DATE
MIKE	ER	1/1/2014
MIKE	ER	1/2/2014
MIKE	OBS	1/20/2014
MIKE	IP	1/28/2014
MIKE	OBS	3/15/2014
MIKE	CLINIC	1/20/2014
MIKE	OP	6/20/2014
MIKE	CLINIC	6/15/2014
DAVID	CLINIC	3/3/2014
DAVID	CLINIC	3/4/2014
DAVID	OP	3/5/2014
DAVID	ER	3/15/2014
DAVID	OBS	3/20/2014
TOM	IP	5/5/2014
TOM	OBS	5/6/2014
TOM	ER	5/8/2014
TOM	OP	9/15/2014

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaauCommented:
Thanks for that. I have corrected the query. Now it looks like this:
;with grp as (
SELECT CASE WHEN Location IN ('ER', 'OBS', 'IP') THEN 'ER' WHEN LOCATION IN('CLINIC', 'OP') THEN 'CLINIC' END GRP, *
FROM TABLE1),
grprn AS(
SELECT ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [DATE]) rn, * FROM grp),
ERs AS(
SELECT * FROM grprn WHERE GRP = 'ER'),
CLs AS(
SELECT * FROM grprn WHERE GRP = 'CLINIC')
SELECT [Name], [Location], [DATE] FROM ERs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] FROM ERs a WHERE RN > 1 AND NOT EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = a.[Name] AND [Date] < DateAdd(hh, 48, a.[Date]))
UNION ALL
SELECT [Name], [Location], [DATE] FROM CLs b WHERE RN > 1 AND NOT EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = b.[Name] AND [Date] < DateAdd(d, 90, b.[Date]))
UNION ALL
SELECT [Name], [Location], [DATE] FROM CLs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] FROM ERs c WHERE RN > 1 AND NOT EXISTS(SELECT 1 FROM CLs WHERE RN = 1 AND [Name] = c.[Name] AND [Date] < DateAdd(hh, 48, c.[Date]))
ORDER BY 1, 3
                                          

Open in new window

0
chaauCommented:
No, I am not grouping. I am just using the expression to build a "computed column" GRP, that I use later on in the code.
SQL Fiddle
0
moe57Author Commented:
i have tested and i am not getting this result if the logic is followed properly
NAME	LOCATION	DATE
MIKE	ER	1/1/2014
MIKE	OBS	1/20/2014
MIKE	IP	1/28/2014
MIKE	OBS	3/15/2014
MIKE	OP	6/20/2014
MIKE	CLINIC	6/15/2014
DAVID	CLINIC	3/3/2014
DAVID	ER	3/15/2014
DAVID	OBS	3/20/2014
TOM	IP	5/5/2014
TOM	ER	5/8/2014
TOM	OP	9/15/2014

Open in new window

the 48 hours logic is not working and the the clinic group should only show up one time.  for instance, if patient goes the clinic group multiple time then we only need to display the first one.  Please see the desired result output.  thanks
0
chaauCommented:
You are right. It needs to be more complex, like this:
;with grp as (
SELECT CASE WHEN Location IN ('ER', 'OBS', 'IP') THEN 'ER' WHEN LOCATION IN('CLINIC', 'OP') THEN 'CLINIC' END GRP, *
FROM TABLE1),
grprn AS(
SELECT ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [DATE]) rn, * FROM grp),
ERs AS(
SELECT * FROM grprn WHERE GRP = 'ER'),
CLs AS(
SELECT * FROM grprn WHERE GRP = 'CLINIC')
SELECT [Name], [Location], [DATE] 
FROM ERs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] 
FROM ERs a 
WHERE RN > 1 AND EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = a.[Name])
AND NOT EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = a.[Name] AND a.[Date] < DateAdd(hh, 48, [Date]))
UNION ALL
SELECT [Name], [Location], [DATE] 
FROM CLs b 
WHERE RN > 1 AND EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = b.[Name])
AND NOT EXISTS(SELECT 1 FROM ERs WHERE RN = 1 AND [Name] = b.[Name] AND b.[Date] < DateAdd(d, 90, [Date]))
UNION ALL
SELECT [Name], [Location], [DATE] 
FROM CLs WHERE RN = 1
UNION ALL 
SELECT [Name], [Location], [DATE] 
FROM ERs c 
WHERE RN > 1 AND EXISTS(SELECT 1 FROM CLs WHERE RN = 1 AND [Name] = c.[Name])
AND NOT EXISTS(SELECT 1 FROM CLs WHERE RN = 1 AND [Name] = c.[Name] AND c.[Date] < DateAdd(hh, 48, [Date]))
ORDER BY 1, 3
                                          

Open in new window

SQL Fiddle
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Plain text data isn't really that helpful either.  What's needed is test data in an INSERT format, like this:

INSERT INTO dbo.tablename ( name, location, date )  select 'MIKE', 'ER', '1/1/2014'
INSERT INTO dbo.tablename ( name, location, date )  select 'MIKE', 'OBS', '1/20/2014'
etc.

I often spend more time reformatting to do INSERTs than to write the queries!  It's just not worth it.
0
SimonCommented:
@Moe: Sorry about any confusion over closing out the prevous question. When the specification changes, I feel it should be a new question.
@Experts: This is the previous thread relating to this question

This is my revised code to achieve the desired result:
with cte1 (personID,ItemRank,ItemDate,ItemLocation) 
as
(select 
name
 ,ROW_NUMBER()  OVER (partition by name ORDER BY name,date,location) AS "Row Number"
,date,location
from datasource
)
select itemrank,datasource.* 
from cte1 inner join datasource 
on cte1.personID=datasource.name AND cte1.itemdate=datasource.date

where ItemRank=1
--first visit was ER
or (select itemlocation from cte1 where itemrank=1 and personID=name) in ('ER', 'OBS' ,'IP') AND location in ('ER', 'OBS' ,'IP')
      AND dateadd(d, -2,itemdate) >(select itemdate from cte1 where itemrank=1 and personID=name)
or (select itemlocation from cte1 where itemrank=1 and personID=name) in ('ER', 'OBS' ,'IP') AND location in ('CLINIC','OP')
  AND dateadd(d, -90,itemdate) >(select itemdate from cte1 where itemrank=1 and personID=name)
--first visit was CLINIC
or ((select itemlocation from cte1 where itemrank=1 and personID=name) in ('CLINIC','OP')
  AND dateadd(d, -2,itemdate) >(select itemdate from cte1 where itemrank=1 and personID=name)
  AND location in ('ER', 'OBS' ,'IP'))

Open in new window


This is it on SQLfiddle. I have exactly replicated Moe's set of test data and get the expected 13 results.
0
moe57Author Commented:
thanks so much
0
moe57Author Commented:
chaau,
I believe you can help me on my other problem that i posted yesterday in which i did not get any solution so far and i believe you can help me since the other logic is similar to the one posted here and you already solved for me.   We are trying to see the different results between the two logic's and decide which one to use.   I really appreciate if you can take a look at this problem.  thanks in advance:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28574762.html
0
chaauCommented:
Hi moe57, yes I have seen your question. I was flat out yesterday and didn't have a chance. If it is still not answered till Monday I will have it a go.
0
moe57Author Commented:
chaau, thanks for getting back to me but this is due on Monday actually and would like to get it done before that if possible.  i understand your time is important so just in case you got some time.  thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.