troubleshooting Question

Difficult WITH CTE Question

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft Development
2 Comments1 Solution435 ViewsLast Modified:
How would I go about getting the following select into a Declared @TEMP  TABLE?

Notice that the ;with cte also is joined with another select in a  union all statement


;WITH CTE (
			[Client ID] ,
			[Contact ID] ,
			ContactType ,
			fullName ,
			[Last Name] ,
			Email ,
			Phone ,
			[Phone ext] ,
			Role ,
			Title ,
			ur ,
			Submitted ,
			Locked ,
			ConPCT ,
			Assignment ,
			Working ,
			Pending ,
			AddRemove ,
			ContactVisitSort ,
			id )
AS
	(
	SELECT	vw.[Client ID],
			vw.[Contact ID],
			vw.ContactType,
			vw.fullName,
			vw.[Last Name],
			vw.Email,
			vw.Phone,
			vw.[Phone ext],
			vw.Role,
			vw.Title,
			vw.ur, 
			vw.Submitted,
			vw.Locked,
			vw.ConPCT,
			vw.Assignment,
			vw.Working,
			vw.Pending,
			(	
				CASE WHEN ISNULL(ContactV.id, 0)= 0 AND ISNULL(vw.[Contact ID],0) > 0 THEN 'Add'
				WHEN ISNULL(ContactV.id, 0)> 0 AND ISNULL(vw.[Contact ID],0) > 0 
						AND NOT Exists (Select 1 from dbo.ClientVisitTrackingContacts WHERE vw.[Contact ID] = ContactV.[Contact ID] AND visitID = A.id ) THEN 'Add'
				WHEN ISNULL(vw.[Contact ID],0) = 0 THEN ''
				ELSE 'Remove' END
			) AS AddRemove,
			(
				CASE WHEN ISNULL(ContactV.id, 0) = 0 
				THEN 0 
				ELSE 1 END
			) AS ContactVisitSort,
			A.id
	FROM	dbo.vw_MarketingVisitationContacts AS vw 
	LEFT OUTER JOIN
			 dbo.ClientVisitTrackingContacts AS ContactV ON vw.[Client ID] = ContactV.[Client ID] AND vw.[Contact ID] = ContactV.[Contact ID]
	OUTER APPLY 
		   ( 
		   SELECT id FROM ClientVisitTracking CVT
		   WHERE CVT.[Client ID] = vw.[Client ID]
		   ) A 
	WHERE		(LEN(vw.fullName) > 0)
	)

	SELECT * FROM cte WHERE id = @VisitID
	
	UNION all
	SELECT  [Client ID] ,
			[Contact ID] ,
			'Contact' ContactType,
			ISNULL([First Name],'') + ' ' + [Last Name] FullName,
			[Last Name],
			Email,
			Phone,
			[Phone ext],
			Role ,
			Title ,
			2 ur,
			0 Submitted,
			0 Locked,
			0 ConPCT,
			0 Assignment,
			0 Working,
			0 Pending,
			'Add' AddRemove,
			0 ContactVisitSort,
			@VisitID id
	FROM	dbo.[Client Contacts] C
	WHERE NOT EXISTS (
						SELECT	1
						FROM	 CTE C1
						WHERE	C.[Client ID] = C1.[Client ID] 
							AND C.[Contact ID] = C1.[Contact ID]
						)
	AND C.[Client ID] = @ClientID
	ORDER BY ur,AddRemove DESC, Assignment DESC,[Last Name]
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros