Solved

find first record and group them by locations in sql

Posted on 2014-12-02
13
245 Views
Last Modified: 2014-12-05
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
0
Comment
Question by:moe57
13 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40477729
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
 

Author Comment

by:moe57
ID: 40477759
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
 

Author Comment

by:moe57
ID: 40477764
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 24

Expert Comment

by:chaau
ID: 40477769
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
 
LVL 24

Expert Comment

by:chaau
ID: 40477772
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
 

Author Comment

by:moe57
ID: 40477783
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
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40477800
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40478925
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
 
LVL 18

Expert Comment

by:Simon
ID: 40479558
@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
 

Author Closing Comment

by:moe57
ID: 40479735
thanks so much
0
 

Author Comment

by:moe57
ID: 40483733
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
 
LVL 24

Expert Comment

by:chaau
ID: 40483876
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
 

Author Comment

by:moe57
ID: 40483912
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question