Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag 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

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Larry Brister

ASKER

Well duh on me...