SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with query for 3 most consecutive visit
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  14:40:03  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 07/22/2013 :  14:45:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Show us the table structure and some proper sample data.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:04:51  Show Profile  Reply with Quote
[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
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:18:24  Show Profile  Reply with Quote
how do I post pics? the HTML mode is off?
Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  15:19:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:23:04  Show Profile  Reply with Quote
I dont have DDL statement, I think you got the wrong person may be?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  15:29:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:35:19  Show Profile  Reply with Quote
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

Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:42:48  Show Profile  Reply with Quote
does it matter to you? I just need help to setup the corerct criteria for 3 consecutive visits.
thanks.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/22/2013 :  15:49:55  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  15:52:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:56:24  Show Profile  Reply with Quote
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?
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  15:58:47  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  16:01:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  16:02:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  16:08:12  Show Profile  Reply with Quote
Thank you Sir! how can I post a snaphot of the tables?
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  16:09:34  Show Profile  Reply with Quote
btw, what does "cteSource" stands for ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  16:09:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  16:22:42  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  16:37:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  17:01:16  Show Profile  Reply with Quote
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!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000