Need help with query for 3 most consecutive visit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with query for 3 most consecutive visit

Author

Topic

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 14:40:03

HI,

I am trying to run a query to give me the 3 consecutive visit by a patient with 3 doctor for 2012 and 2013 but dont know how? please help. here is my query:

select distinct p.last_name, p.first_name, p.middle_name
, date_of_birth
, [description] as 'Provider Name'
--, person_nbr
, rendering_provider_id
, pe.person_id
, pt.med_rec_nbr
from patient_encounter pe
inner join provider_mstr pm on pm.provider_id = pe.rendering_provider_id
inner join person p on p.person_id = pe.person_id
inner join patient pt on pt.person_id = p.person_id
where rendering_provider_id in ('4BA41C5A-4096-4AAD-BF83-DF620A065B8F', '675EC766-E8E0-48CD-A532-6056ECE52C4C', 'AE9EA089-6A01-4003-9FA9-E1A613666480')
and pe.enc_timestamp BETWEEN '01/01/2012' and GETDATE()
order by 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 14:45:11

Show us the table structure and some proper sample data.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:04:51

[quote]Originally posted by SwePeso

Show us the table structure and some proper sample data.

how do I post pics? the HTML mode is off?
Thanks

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:18:24

how do I post pics? the HTML mode is off?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 15:19:29

You post your DDL statements like

CREATE TABLE #Sample...
INSERT TABLE #Sample...

And then you show us what the expected output is supposed to be based on the posted sample data.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:23:04

I dont have DDL statement, I think you got the wrong person may be?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 15:29:11

Ok, let's try this in another fashion.
What is your definition for "consecutive visits"?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:35:19

3 consecutive visit with the same provider. how do i post a pic on this replies? here is my sub query: Sorry I ahve to turncate the person ids because of security reason.

select pm.[description], pm.provider_id, pe.enc_timestamp, pe.person_id
from patient_encounter pe
inner join provider_mstr pm on pm.provider_id = pe.rendering_provider_id
where pe.person_id = '1B36-44A8-'
and pe.enc_timestamp BETWEEN '01/01/2012' and GETDATE()
and pm.provider_id in ('-BF83-DF620A065B8F',
'-E1A613666480')
order by 1, pe.enc_timestamp desc

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:42:48

does it matter to you? I just need help to setup the corerct criteria for 3 consecutive visits.
thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 15:49:55

quote:
Originally posted by Ramin

does it matter to you? I just need help to setup the corerct criteria for 3 consecutive visits.
thanks.

The reason Swepeso is asking for the definition of consecutive visits is that unless one knows what that definition is, there is no way to write a query to get that information.

Does consecutive visits mean visits on 3 consecutive days? Or does it mean 3 consecutive visits with the same doctor, with no visits to any other doctor in between? Or does it mean 3 visits with 3 different doctors? Or some combination of the above? What would be considered a non-consecutive visit?

How one writes a query depeneds precisely on that definition.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 15:52:22

[code]DECLARE @Sample TABLE
(
PersonID INT NOT NULL,
ProviderID INT NOT NULL,
Visit DATETIME NOT NULL
);

INSERT @Sample
(
PersonID,
ProviderID,
Visit
)
VALUES (1, 3, '20130101'),
(1, 3, '20130201'),
(1, 3, '20130301'),
(3, 6, '20130101'),
(3, 6, '20130201'),
(3, 6, '20130301'),
(3, 6, '20130401'),
(3, 6, '20130501'),
(2, 1, '20130101'),
(2, 1, '20130201'),
(2, 3, '20130301'),
(2, 1, '20130401');

-- SwePeso
WITH cteSource(PersonID, ProviderID, Visit, byPerson, byProvider)
AS (
SELECT PersonID,
ProviderID,
Visit,
ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Visit) AS byPerson,
ROW_NUMBER() OVER (PARTITION BY PersonID, ProviderID ORDER BY Visit) AS byProvider
FROM @Sample
)
SELECT PersonID,
ProviderID,
COUNT(*) AS Visits,
MIN(Visit) AS FromDate,
MAX(Visit) AS ToDate
FROM cteSource
GROUP BY PersonID,
ProviderID,
byPerson - byProvider
HAVING COUNT(*) >= 3;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:56:24

OK thaks! What I mean is 3 consecutive visits with the same provider, there are 3 providers. so When you run the query it shows up in the list of 3 providers. so I need to know 3 consecutive vissit by the same person with one or 3 providesr between 20120-2013. Hope I have clarified this?

How can I post a pic of the table here?

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 15:58:47

Thanks you SwePeso!! let me try this and see if this works. just one thing that in WITH cteSource(PersonID, ProviderID, Visit, byPerson, byProvider)
the visit should be enc_timestamp. is this what you meant? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 16:01:02

Since you couldn't provide ddl I had to use commonly used names for the columns.
You have to alter the solution to fit your environment.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 16:02:29

[code]-- SwePeso
WITH cteSource(PersonID, ProviderID, Visit, byPerson, byProvider)
AS (
SELECT PersonID,
ProviderID,
Visit,
ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Visit) AS byPerson,
ROW_NUMBER() OVER (PARTITION BY PersonID, ProviderID ORDER BY Visit) AS byProvider
FROM @Sample
WHERE Visit >= '20120101'
AND Visit < '20140101'
)
SELECT PersonID,
ProviderID,
COUNT(*) AS Visits,
MIN(Visit) AS FromDate,
MAX(Visit) AS ToDate
FROM cteSource
GROUP BY PersonID,
ProviderID,
byPerson - byProvider
HAVING COUNT(*) >= 3;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 16:08:12

Thank you Sir! how can I post a snaphot of the tables?

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 16:09:34

btw, what does "cteSource" stands for ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 16:09:51

You don't. You post proper sample data as I requested before, so that we can use Copy & Paste to use the data.
We volunteer here in our spare time. We don't want to spend hours typing data which can easily be copied.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 16:22:42

SwePeso, Thanks I know I could not provide you with sample data as I could not copy paet it inot the window.
any way to get ride of VALUES (1, 3, '20130101'),
(1, 3, '20130201'),
(1, 3, '20130301'),
(3, 6, '20130101'),
(3, 6, '20130201'),
(3, 6, '20130301'),
(3, 6, '20130401'),
(3, 6, '20130501'),
(2, 1, '20130101'),
(2, 1, '20130201'),
(2, 3, '20130301'),
(2, 1, '20130401');

and just declare the date like between 2012 to 2013?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 16:37:48

Let me know if I can spoonfeed you more.

-- SwePeso
WITH cteSource(Person_ID, Rendering_Provider_ID, Enc_Timestamp, byPerson, byProvider)
AS (
	SELECT	Person_ID,
		Rendering_Provider_ID,
		Enc_Timestamp,
		ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Enc_Timestamp) AS byPerson,
		ROW_NUMBER() OVER (PARTITION BY Person_ID, Rendering_Provider_ID ORDER BY Enc_Timestamp) AS byProvider
	FROM	dbo.Patient_Encounter  
	WHERE	Rendering_Provider_ID IN ('4BA41C5A-4096-4AAD-BF83-DF620A065B8F', '675EC766-E8E0-48CD-A532-6056ECE52C4C', 'AE9EA089-6A01-4003-9FA9-E1A613666480')
		AND Enc_Timestamp >= '20120101'
		AND Enc_Timestamp < '20140101'
)
SELECT		Person_ID,
		Rendering_Provider_ID,
		COUNT(*) AS Visits,
		MIN(Enc_Timestamp) AS FromDate,
		MAX(Enc_Timestamp) AS ToDate
FROM		cteSource
GROUP BY	Person_ID,
		Rendering_Provider_ID,
		byPerson - byProvider
HAVING		COUNT(*) >= 3;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ramin
Starting Member

26 Posts

Posted - 2013-07-22 : 17:01:16

Thank you! Just got confuse as the first part and second part you sent from your original post. I wasnt sure if I needed the first part, thats why I asked!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources