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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Ramin
Starting Member

26 Posts

Posted - 07/22/2013 :  18:59:50  Show Profile  Reply with Quote
SWePeso, Can you tell me what does the WITH cteSource does? I hvae never used this before? thnx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  19:25:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It creates a local view, accessible only from the current statement.
It's known a Common Table Expression.


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 :  19:43:54  Show Profile  Reply with Quote
ok, casue I am trying to add more to the list such as med_rec_nbr, it says WITH cteSource(PersonID, Med_rec_nbr, ProviderID, Visit, byPerson, byProvider) say cteSource has fewer columns than it specified in the list? I thought its limited with the columns?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/23/2013 :  01:05:21  Show Profile  Reply with Quote
quote:
Originally posted by Ramin

ok, casue I am trying to add more to the list such as med_rec_nbr, it says WITH cteSource(PersonID, Med_rec_nbr, ProviderID, Visit, byPerson, byProvider) say cteSource has fewer columns than it specified in the list? I thought its limited with the columns?


it is not
Only thing is you should include all the columns in its definition as you're including in SELECT list inside the CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/23/2013 :  12:21:33  Show Profile  Reply with Quote
Thanks Visakh.. I have tried including it inside the CTE lke--> WITH cteSource(PersonID, Med_rec_nbr, ProviderID, Visit, byPerson, byProvider) AS ( Select person_id,
Rendering_provider_id, Enc_timestamp, med_rec_nbr, as soon as I put the med_rec_nbr, it complains??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/23/2013 :  12:30:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have to have the same column order...


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

Ramin
Starting Member

26 Posts

Posted - 07/23/2013 :  12:40:17  Show Profile  Reply with Quote
Ok this is how I am doing it:
WITH cteSource(Person_ID, med_rec_nbr, Rendering_Provider_ID, Enc_Timestamp, byPerson, byProvider)
AS (
SELECT Person_ID,
med_rec_nbr, ---> this is where I get red underline and wont take it?
Rendering_Provider_ID,
Enc_Timestamp,
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/23/2013 :  13:11:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Most probably your column med_rec_nbr is stored in another table than the one used in the CTE.
You will need a JOIN. But this is currently over your head. Use the CTE as is and store the intermediate result in a table.
Then use that table to find out the information you want.



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

Ramin
Starting Member

26 Posts

Posted - 07/23/2013 :  14:25:00  Show Profile  Reply with Quote
Yes you right its not there. it has to be joined. I had it joined in my first sql I sent out. now I have to figure out how to include med_rec_nbr in there. thnx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/23/2013 :  14:37:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Persons TABLE
	(
		PersonID INT NOT NULL,
		ProviderID INT NOT NULL,
		Visits INT NOT NULL,
		FromDate DATETIME NOT NULL,
		ToDate DATETIME NOT NULL
	);

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'
)
INSERT		@Persons
		(
			PersonID,
			ProviderID,
			Visits,
			FromDate,
			ToDate
		)
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;

SELECT DISTINCT	p.Last_Name,
		p.First_Name,
		p.Middle_Name,
		p.Date_Of_Birth,
		pm.[Description] AS [Provider Name],
		pe.Rendering_Provider_ID,
		pe.Person_ID,
		pt.Med_Rec_Nbr
FROM		dbo.Patient_Encounter AS pe 
INNER JOIN	dbo.Provider_Mstr AS pm ON pm.Provider_ID = pe.Rendering_Provider_ID
INNER JOIN	dbo.Person AS p ON p.Person_ID = pe.Person_ID
INNER JOIN	dbo.Patient AS pt ON pt.Person_ID = p.Person_ID
INNER JOIN	@Persons AS w ON w.PersonID = pe.Person_ID
			AND w.ProviderID = pe.Rendering_Provider_ID
			AND w.FromDate <= pe.Enc_Timestamp
			AND w.ToDate >= pe.Enc_Timestamp
ORDER BY	p.Last_Name;



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

Ramin
Starting Member

26 Posts

Posted - 07/23/2013 :  16:59:46  Show Profile  Reply with Quote
Thanks SwePeso! Let me try this to see if it works. I appreciate!!
Go to Top of Page

Ramin
Starting Member

26 Posts

Posted - 07/23/2013 :  17:08:24  Show Profile  Reply with Quote
This is what I get:

Msg 206, Level 16, State 2, Line 11
Operand type clash: uniqueidentifier is incompatible with int
Msg 206, Level 16, State 2, Line 42
Operand type clash: uniqueidentifier is incompatible with int
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/23/2013 :  18:45:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now is the time when you step up and change the datatype in the @Persons table, from INT to UNIQUEIDENTIFIER where appropriate.
Hint, change the datatype for the ProviderID column.




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

Ramin
Starting Member

26 Posts

Posted - 07/24/2013 :  17:02:01  Show Profile  Reply with Quote
will do.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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