Avatar of moe57
moe57
 asked on

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
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
moe57

8/22/2022 - Mon
chaau

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

moe57

ASKER
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
moe57

ASKER
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
chaau

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

chaau

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
moe57

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

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.
Simon

@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.
moe57

ASKER
thanks so much
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
moe57

ASKER
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:
https://www.experts-exchange.com/Database/MS-SQL-Server/Q_28574762.html
chaau

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.
moe57

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.