Query with multiple rows

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

That's all right. Take your time and let us know in case of any issues.

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

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

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

Select allOpen in new window

Hey Robb,

Is that worked or do you need any help ?

Pawan and Olaf....you guys saved me...I really appreciate it!!!

You're welcome.

Glad to help as always.

I just tried the CTE version of this...and im getting ...invalid column name a.nid

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

Select allOpen in new window

nevermind..i corrected

cool :)

yea..this is not yeilding same results...not sure

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen in new window

Well I pasted that wrong..but the CTE last updated does not work

Please provide code which is not working.

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?

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

Select allOpen in new window

well that should not create any issues.

a.nid was changed from c.nid....that means different things

At which place.

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

Select allOpen in new window

You have commented this line

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

pls uncomment this.

thats just for my testing....my test of slowness a date in their

Im sorry..I did have date commented...Im surprised Report Server didnt throw me an error

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...

Welcome.

Cheers :)

Pawan:

So what was the value doing the joins this way versus the In clause?

>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.