Query with multiple rows

I have the following query.  I need to get the columng ExtContact to roll up into one row and be comma delimited.

select a.cType,vxc.ExtContact from tAction a
LEFT OUTER JOIN vActivityExtContacts vxc
                              ON a.nid = vxc.nidAction


Here is an idea of sample data

Current output sample
Meeting     User1
Meeting     User2
Meeting2   user1
Meeting2   user2
Meeting2   user3

Desired results
Meeting    User1,User2
Meeting2  User1,User2,User3
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
This needs some of these hardly to explain XML Path queries...

Declare @tAction as Table (nid int, cType varchar(10))
Declare @vActivityExtContacts as Table (nidAction int, ExtContact varchar(10))

Insert Into @tAction Values (1,'Meeting'),(2,'Meeting2');
Insert Into @vActivityExtContacts Values (1,'User1'),(1,'User2'),(2,'User1'),(2,'User2'),(2,'User3');

--the essential query:
Select a.cType, 
    Stuff( 
	       (
		     Select ',' + vxc.ExtContact 
			 from @vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nid 
			 For XML path('')
		   ) 
	     ,1,1,'')  as participants
from @tAction a

Open in new window


Bye, Olaf.
1
Pawan KumarDatabase ExpertCommented:
Please try this. You need to use a CTE. It is also using the XML path method as shown above. Note we have to use the group by/distinct else we will get multiple records.

I have modified your code to what you need.

;WITH CTE AS
(
	select a.cType,vxc.ExtContact from tAction a
	LEFT OUTER JOIN vActivityExtContacts vxc
	ON a.nid = vxc.nidAction
)
SELECT b.cType 
            , STUFF 
                ((
					SELECT ', ' + ExtContact 
					FROM CTE a
					WHERE ( a.cType = b.cType )
					FOR XML PATH('')
                ) ,1,2,'') 
                AS ExtContact
FROM CTE b
GROUP BY b.cType

Open in new window


You can also read more about this idea from - https://msbiskills.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
2
Olaf DoschkeSoftware DeveloperCommented:
My result:
query resultof my sample code
Without using a CTE or group by.

If you get something wrong, Robb, please merely give feedback. It should work the same way even in older SQL Server versions, stuff() and XML path are both quite old.

All you need to do to make this work with your tables is take the query after the comment "the essential query" and remove the @ signs I used for my table variables so that I can work with sample data in the most straightforward manner. You surely figure(d) that out. If the names of tables and columns you gave are correct, that's it. The upper part is merely to test and demo it works as a whole sample code and is tested. simply copy it 1:1 to a query window in SSMS and you surely get the same result as me.

Bye, Olaf.
1
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Robb HillSenior .Net DeveloperAuthor Commented:
I am testing this monrning....I had a feeling it was something like this..but my test query bombed my SQL Server..I think I was missing the cte.

I will comment shortly.   Thanks
0
Pawan KumarDatabase ExpertCommented:
That's all right. Take your time and let us know in case of any issues.
0
Robb HillSenior .Net DeveloperAuthor Commented:
I reduced the complexity of the query so you all could see what I was trying to do....This cte ...will not work in current form ...because the actual query is more complex.  

Here is entire query.  This query would return one row...until I add the join in this sample.


SELECT      c.nid AS nidIntContact,
                    c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
                  a.cClassify AS Classification,
                  a.cType as [Type],
                  a.dStop as [Date],
                  a.nCalcHours as [Hours],                     
                  c.lActive,
                  isnull(vxc.ExtContact,'') ExtContact,                   
            isNull(vxc.ExtEntity,'') ExtEntity,                         
                  a.cSubject as [Project],
                  a.mNote as [Notes]
            
                  
                       
FROM        cadoc_crm..tContact c
                        INNER JOIN  tActionXContact  ac
                              ON c.nid = ac.nidContact
                        INNER JOIN tAction a
                              ON ac.nidAction = a.nid
                        INNER JOIN  cadoc_crm..tClient cl
                              ON c.nidClient = cl.nid
                        LEFT OUTER JOIN vActivityExtContacts vxc
                              ON a.nid = vxc.nidAction
       
where
cl.cSiteCode = @cSiteCode
and c.nid IN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )
and a.cType IN ( SELECT value FROM fn_Split(@Type,',') )
and a.cClassify IN ( SELECT value FROM fn_Split(@Classification,',') )
and a.dStop between @StartDate and @EndDate
order by c.cLastName, cFirstName
0
Robb HillSenior .Net DeveloperAuthor Commented:
I tried this...can this ...just made the extContact create in infintie number of comma delimited contacts.  Very bad..lucky I just did top 10.

SELECT     top 10 c.nid AS nidIntContact,
                    c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
                  a.cClassify AS Classification,
                  a.cType as [Type],
                  a.dStop as [Date],
                  a.nCalcHours as [Hours],                     
                  c.lActive,
                  oa1.ExtContact,                   
            --isNull(vxc.ExtEntity,'') ExtEntity,                         
                  a.cSubject as [Project],
                  a.mNote as [Notes]
            
                  
                       
FROM        cadoc_crm..tContact c
                        INNER JOIN  tActionXContact  ac
                              ON c.nid = ac.nidContact
                        INNER JOIN tAction a
                              ON ac.nidAction = a.nid
                        INNER JOIN  cadoc_crm..tClient cl
                              ON c.nidClient = cl.nid
                        OUTER APPLY (
                          SELECT
                                    STUFF
                                          ((
                                            SELECT ', ' + ExtContact
                                            from tAction a
                                                LEFT OUTER JOIN vActivityExtContacts vxc
                                                      ON a.nid = vxc.nidAction            
                                            FOR xml PATH ('')
                                     ), 1, 2, '') AS [ExtContact]
                          ) oa1
0
Robb HillSenior .Net DeveloperAuthor Commented:
SELECT     top 10 c.nid AS nidIntContact, 
		  	c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
			a.cClassify AS Classification,
			a.cType as [Type],
			a.dStop as [Date],
			a.nCalcHours as [Hours], 			  
			c.lActive,
			oa1.ExtContact, 			
            --isNull(vxc.ExtEntity,'') ExtEntity, 				
			a.cSubject as [Project],
			a.mNote as [Notes]
		
			
                        
FROM        cadoc_crm..tContact c 
				INNER JOIN  tActionXContact  ac 
					ON c.nid = ac.nidContact 
				INNER JOIN tAction a 
					ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl
					ON c.nidClient = cl.nid 
				OUTER APPLY (
				  SELECT
						STUFF
							((
							  SELECT ', ' + ExtContact 
							  from tAction a
								LEFT OUTER JOIN vActivityExtContacts vxc
									ON a.nid = vxc.nidAction            
							  FOR xml PATH ('')
						 ), 1, 2, '') AS [ExtContact]
				  ) oa1

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Hey Robb,

Is that worked or do you need any help ?
0
Olaf DoschkeSoftware DeveloperCommented:
Robb,

take a close look at my solution. It doesn't do a join, it gets the data from vActivityExtContacts within the stuff only, so keep your query as is and only add the stuff call for the additional field you want.

SELECT      c.nid AS nidIntContact, 
                    c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
                  a.cClassify AS Classification,
                  a.cType as [Type],
                  a.dStop as [Date],
                  a.nCalcHours as [Hours],                     
                  c.lActive,
                  Stuff( 
	          (
		     Select ', ' + vxc.ExtContact 
			 from vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nid 
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtContacts,
                  Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nid AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities,                  
                  a.cSubject as [Project],
                  a.mNote as [Notes]
FROM        cadoc_crm..tContact c 
                        INNER JOIN  tActionXContact  ac 
                              ON c.nid = ac.nidContact 
                        INNER JOIN tAction a 
                              ON ac.nidAction = a.nid 
                        INNER JOIN  cadoc_crm..tClient cl
                              ON c.nidClient = cl.nid 
where 
cl.cSiteCode = @cSiteCode
and c.nid IN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )
and a.cType IN ( SELECT value FROM fn_Split(@Type,',') )
and a.cClassify IN ( SELECT value FROM fn_Split(@Classification,',') )
and a.dStop between @StartDate and @EndDate
order by c.cLastName, cFirstName

Open in new window


I also aded a second Stuff for ExtEntity. See? You do a subquery to get the comma separated list of ExtContact names. You don't need to join vActivityExtContacts  to the whole query, that just causes your multiplication of records by count of participants you don't want, you don't join first, then summarize, you "join" - or better said you subquery the summary.

You might want to change this into one STUFF of ExtContact with ExtEntity in brackets to integrate that into one list.

Bye, Olaf.
0
Pawan KumarDatabase ExpertCommented:
Please try this -

Do not use in clause, Use Exists or INNER join. Also use CTE for simplicity.

;WITH CTE AS
(
	SELECT      
				c.nid AS nidIntContact, 
				c.cLastName + ', ' + c.cFirstName + 
						(CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
				a.cClassify AS Classification,
				a.cType as [Type],
				a.dStop as [Date],
				a.nCalcHours as [Hours],                     
				c.lActive,      
				a.cSubject as [Project],
				a.mNote as [Notes]
	FROM        cadoc_crm..tContact c 
				INNER JOIN  tActionXContact  ac ON c.nid = ac.nidContact 
				INNER JOIN tAction a ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl  ON c.nidClient = cl.nid 
				INNER JOIN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )x ON c.nid = x.nValue
				INNER JOIN ( SELECT value FROM fn_Split(@Type,',') )y ON a.cType = y.Value
				INNER JOIN ( SELECT value FROM fn_Split(@Classification,',') )u ON a.cClassify = u.value
	where 
	cl.cSiteCode = @cSiteCode
	and a.dStop between @StartDate and @EndDate
)
SELECT * , Stuff( 
	          (
				 Select ', ' + vxc.ExtContact 
				 from vActivityExtContacts vxc 
				 Where vxc.nidAction = a.nid 
				 For XML path('')
				) 
	          ,1,2,'')  as ExtContacts,
              Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nid AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities
FROM CTE a

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robb HillSenior .Net DeveloperAuthor Commented:
Pawan and Olaf....you guys saved me...I really appreciate it!!!
0
Olaf DoschkeSoftware DeveloperCommented:
You're welcome.
0
Pawan KumarDatabase ExpertCommented:
Glad to help as always.
0
Robb HillSenior .Net DeveloperAuthor Commented:
I just tried the CTE version of this...and im getting ...invalid column name a.nid
0
Robb HillSenior .Net DeveloperAuthor Commented:
USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 9:24:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

	SET NOCOUNT ON;




;WITH CTE AS
(
	SELECT      
				c.nid AS nidIntContact, 
				c.cLastName + ', ' + c.cFirstName + 
						(CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
				a.cClassify AS Classification,
				a.cType as [Type],
				a.dStop as [Date],
				a.nCalcHours as [Hours],                     
				c.lActive,      
				a.cSubject as [Project],
				a.mNote as [Notes]
	FROM        cadoc_crm..tContact c 
				INNER JOIN  cadoc_crm..tActionXContact  ac ON c.nid = ac.nidContact 
				INNER JOIN cadoc_crm..tAction a ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl  ON c.nidClient = cl.nid 
				INNER JOIN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )x ON c.nid = x.nValue
				INNER JOIN ( SELECT value FROM fn_Split(@Type,',') )y ON a.cType = y.Value
				INNER JOIN ( SELECT value FROM fn_Split(@Classification,',') )u ON a.cClassify = u.value
	where 
	cl.cSiteCode = @cSiteCode
	and a.dStop between @StartDate and @EndDate
)
SELECT * , Stuff( 
	          (
				 Select ', ' + vxc.ExtContact 
				 from cadoc_crm..vActivityExtContacts vxc 
				 Where vxc.nidAction = a.nid 
				 For XML path('')
				) 
	          ,1,2,'')  as ExtContacts,
              Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from cadoc_crm..vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nid AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities
FROM CTE a



end

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
nevermind..i corrected
0
Pawan KumarDatabase ExpertCommented:
cool :)
0
Robb HillSenior .Net DeveloperAuthor Commented:
yea..this is not yeilding same results...not sure
0
Pawan KumarDatabase ExpertCommented:
Updated.

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 9:24:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

	SET NOCOUNT ON;




;WITH CTE AS
(
	SELECT      
				c.nid AS nidIntContact, 
				c.cLastName + ', ' + c.cFirstName + 
						(CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
				a.cClassify AS Classification,
				a.cType as [Type],
				a.dStop as [Date],
				a.nCalcHours as [Hours],                     
				c.lActive,      
				a.cSubject as [Project],
				a.mNote as [Notes]
	FROM        cadoc_crm..tContact c 
				INNER JOIN  cadoc_crm..tActionXContact  ac ON c.nid = ac.nidContact 
				INNER JOIN cadoc_crm..tAction a ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl  ON c.nidClient = cl.nid 
				INNER JOIN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )x ON c.nid = x.nValue
				INNER JOIN ( SELECT value FROM fn_Split(@Type,',') )y ON a.cType = y.Value
				INNER JOIN ( SELECT value FROM fn_Split(@Classification,',') )u ON a.cClassify = u.value
	where 
	cl.cSiteCode = @cSiteCode
	and a.dStop between @StartDate and @EndDate
)
SELECT * , Stuff( 
	          (
				 Select ', ' + vxc.ExtContact 
				 from cadoc_crm..vActivityExtContacts vxc 
				 Where vxc.nidAction = a.nidIntContact 
				 For XML path('')
				) 
	          ,1,2,'')  as ExtContacts,
              Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from cadoc_crm..vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nidIntContact AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities
FROM CTE a



end

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
The CTE version versus the previous with only the STUFF doesnt show the multiple contacts comma delimited..they are just blank rows .


Not sure what in the logic is breaking it.


This seems to work:

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 8:51:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

 SET NOCOUNT ON;

SELECT      c.nid AS nidIntContact, 
                    c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
                  a.cClassify AS Classification,
                  a.cType as [Type],
                  a.dStop as [Date],
                  a.nCalcHours as [Hours],                    
                  c.lActive,
                  Stuff( 
          (
      Select ', ' + vxc.ExtContact 
    from vActivityExtContacts vxc 
    Where vxc.nidAction = a.nid 
    For XML path('')
    ) 
          ,1,2,'')  as ExtContacts,
                  Stuff( 
          (
      Select ', ' + vxc.ExtEntity
    from vActivityExtContacts vxc 
    Where vxc.nidAction = a.nid AND NOT vxc.ExtEntity IS NULL
    For XML path('')
    ) 
          ,1,2,'')  as ExtEntities,                  
                  a.cSubject as [Project],
                  a.mNote as [Notes]
FROM        cadoc_crm..tContact c 
                        INNER JOIN  tActionXContact  ac 
                              ON c.nid = ac.nidContact 
                        INNER JOIN tAction a 
                              ON ac.nidAction = a.nid 
                        INNER JOIN  cadoc_crm..tClient cl
                              ON c.nidClient = cl.nid 


where 
--a.cClassify like '%BizDev%'and 
cl.cSiteCode = @cSiteCode
and c.nid IN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )
and a.cType IN ( SELECT value FROM fn_Split(@Type,',') )
and a.cClassify IN ( SELECT value FROM fn_Split(@Classification,',') )
and a.dStop between @StartDate and @EndDate
order by c.cLastName, cFirstName



end

Open in new window



This does not

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 8:51:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

 SET NOCOUNT ON;

SELECT      c.nid AS nidIntContact, 
                    c.cLastName + ', ' + c.cFirstName + (CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
                  a.cClassify AS Classification,
                  a.cType as [Type],
                  a.dStop as [Date],
                  a.nCalcHours as [Hours],                    
                  c.lActive,
                  Stuff( 
          (
      Select ', ' + vxc.ExtContact 
    from vActivityExtContacts vxc 
    Where vxc.nidAction = a.nid 
    For XML path('')
    ) 
          ,1,2,'')  as ExtContacts,
                  Stuff( 
          (
      Select ', ' + vxc.ExtEntity
    from vActivityExtContacts vxc 
    Where vxc.nidAction = a.nid AND NOT vxc.ExtEntity IS NULL
    For XML path('')
    ) 
          ,1,2,'')  as ExtEntities,                  
                  a.cSubject as [Project],
                  a.mNote as [Notes]
FROM        cadoc_crm..tContact c 
                        INNER JOIN  tActionXContact  ac 
                              ON c.nid = ac.nidContact 
                        INNER JOIN tAction a 
                              ON ac.nidAction = a.nid 
                        INNER JOIN  cadoc_crm..tClient cl
                              ON c.nidClient = cl.nid 


where 
--a.cClassify like '%BizDev%'and 
cl.cSiteCode = @cSiteCode
and c.nid IN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )
and a.cType IN ( SELECT value FROM fn_Split(@Type,',') )
and a.cClassify IN ( SELECT value FROM fn_Split(@Classification,',') )
and a.dStop between @StartDate and @EndDate
order by c.cLastName, cFirstName



end

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
Well I pasted that wrong..but the CTE last updated does not work
0
Pawan KumarDatabase ExpertCommented:
Please provide code which is not working.
0
Robb HillSenior .Net DeveloperAuthor Commented:
Those items you changed to inner joins ....the value in those are coming from a list of items..in a dropdown from Reporting Server....does that make sense to join that way?
0
Robb HillSenior .Net DeveloperAuthor Commented:
This does not work as compared to the query with just the stuff

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 9:24:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

	SET NOCOUNT ON;




;WITH CTE AS
(
	SELECT      
				c.nid AS nidIntContact, 
				c.cLastName + ', ' + c.cFirstName + 
						(CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
				a.cClassify AS Classification,
				a.cType as [Type],
				a.dStop as [Date],
				a.nCalcHours as [Hours],                     
				c.lActive,      
				a.cSubject as [Project],
				a.mNote as [Notes]
	FROM        cadoc_crm..tContact c 
				INNER JOIN  cadoc_crm..tActionXContact  ac ON c.nid = ac.nidContact 
				INNER JOIN cadoc_crm..tAction a ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl  ON c.nidClient = cl.nid 
				INNER JOIN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )x ON c.nid = x.nValue
				INNER JOIN ( SELECT value FROM fn_Split(@Type,',') )y ON a.cType = y.Value
				INNER JOIN ( SELECT value FROM fn_Split(@Classification,',') )u ON a.cClassify = u.value
	where 
	cl.cSiteCode = @cSiteCode
	and a.dStop between @StartDate and @EndDate
)
SELECT * , Stuff( 
	          (
				 Select ', ' + vxc.ExtContact 
				 from cadoc_crm..vActivityExtContacts vxc 
				 Where vxc.nidAction = a.nidIntContact 
				 For XML path('')
				) 
	          ,1,2,'')  as ExtContacts,
              Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from cadoc_crm..vActivityExtContacts vxc 
			 Where vxc.nidAction = a.nidIntContact AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities
FROM CTE a



end

Open in new window

0
Pawan KumarDatabase ExpertCommented:
well that should not create any issues.
0
Robb HillSenior .Net DeveloperAuthor Commented:
a.nid was changed from c.nid....that means different things
0
Pawan KumarDatabase ExpertCommented:
At which place.
0
Robb HillSenior .Net DeveloperAuthor Commented:
The NIds got confused on the refactor...I changed and its running with same data now..but is much slower.  Should this run faster...I am doing an index check now...but the other ran much faster

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spActivityByProfessional]    Script Date: 11/2/2017 10:26:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActivityByProfessional] 

@cSiteCode varchar(20)
,@IntContact varchar(max) = ''
,@Type varchar(max) = ''
,@Classification varchar(max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS
BEGIN

 SET NOCOUNT ON;

;WITH CTE AS
(
	SELECT      
				c.nid AS nidIntContact,
				a.nid as actionId, 
				c.cLastName + ', ' + c.cFirstName + 
						(CASE WHEN c.lActive = 0 THEN ' (inactive)' ELSE '' END) AS Professional,
				a.cClassify AS Classification,
				a.cType as [Type],
				a.dStop as [Date],
				a.nCalcHours as [Hours],                     
				c.lActive,      
				a.cSubject as [Project],
				a.mNote as [Notes]
	FROM        cadoc_crm..tContact c 
				INNER JOIN  cadoc_crm..tActionXContact  ac ON c.nid = ac.nidContact 
				INNER JOIN cadoc_crm..tAction a ON ac.nidAction = a.nid 
				INNER JOIN  cadoc_crm..tClient cl  ON c.nidClient = cl.nid 
				INNER JOIN ( SELECT nValue FROM dbo.udfSplitListIntoTableInt(@IntContact,',') )x ON c.nid = x.nValue
				INNER JOIN ( SELECT value FROM fn_Split(@Type,',') )y ON a.cType = y.Value
				INNER JOIN ( SELECT value FROM fn_Split(@Classification,',') )u ON a.cClassify = u.value
	where 
	cl.cSiteCode = 'Root'
	--and a.dStop between @StartDate and @EndDate
)
SELECT * , Stuff( 
	          (
				 Select ', ' + vxc.ExtContact 
				 from cadoc_crm..vActivityExtContacts vxc 
				 Where vxc.nidAction = a.actionId
				 For XML path('')
				) 
	          ,1,2,'')  as ExtContacts,
              Stuff( 
	          (
		     Select ', ' + vxc.ExtEntity
			 from cadoc_crm..vActivityExtContacts vxc 
			 Where vxc.nidAction = a.actionId AND NOT vxc.ExtEntity IS NULL
			 For XML path('')
		   ) 
	          ,1,2,'')  as ExtEntities
FROM CTE a



end

Open in new window

0
Pawan KumarDatabase ExpertCommented:
You have commented this line

--and a.dStop between @StartDate and @EndDate

pls uncomment this.
0
Robb HillSenior .Net DeveloperAuthor Commented:
thats just for my testing....my test of slowness a date in their
0
Robb HillSenior .Net DeveloperAuthor Commented:
Im sorry..I did have date commented...Im surprised Report Server didnt throw me an error
0
Robb HillSenior .Net DeveloperAuthor Commented:
ok so this is really great!!!

So can we recap the gains here on why we did the joins the way we did etc...this was a good learning exercise for me...
0
Pawan KumarDatabase ExpertCommented:
Welcome.

Cheers :)
0
Robb HillSenior .Net DeveloperAuthor Commented:
Pawan:

So what was the value doing the joins this way versus the In clause?
0
Olaf DoschkeSoftware DeveloperCommented:
>I just tried the CTE version of this...and im getting ...invalid column name a.nid
I see Pawan already corrected that, but indeed his CTE does not contain a ni column, as it joins via a.nid but doesn't put this into the CTE columns. Pawan, you were also lucky c.nid AS nidIntContact renames the tContac.nid , so it is later not confused with tAction.nid.

Robb, why do you expect the CTE to run faster? As already said it first pulls all data, to finally aggregate. This is not limited to the ExtContact or ExtEntity column but all columns. The Final query using the CTE intermediate result then reduces via group by.

Often enough the query optimization can make this good, but not always.

Nevermind.

Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.