Solved

find first record and group them by locations in sql

Posted on 2014-12-02
13
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 25

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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 25

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 25

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 25

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 25

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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