Avatar of Larry Brister
Larry Brister
Flag for United States of America asked on

Difficult WITH CTE Question

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]

Open in new window

Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft Development

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Larry Brister

ASKER
Well duh on me...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy