Solved

find first record and group them by locations in sql

Posted on 2014-12-02
13
237 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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:ScottPletcher
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:SimonAdept
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now