• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Adding Columns derived by Common Table Expressions

I have a CTE that Generates the columns Franchisee_ID, Franchisee_Name and Job Count.

I then create a select expression that references this CTE and users 2 subqueries to generate a table that includes the 3 columns from the CTE plus Self_Gen_Count  and Call_Centre_Count.  I would like to add another column that adds up the Self_Gen_Count and the Call_Centre_Count..  The only way that I can figure out how to do this is to tediously add the two select statements used to generate these colums to get the new column Appointments as shown in the attached query.  Is there any way that I can simply just refer to these colums to get the new column that I need?  Any help appreciated.

rbs  
ALTER PROCEDURE [dbo].[_CloseRate_Top5_Get]
@startDate DateTime,
@endDate DateTime

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    WITH NewJobs(Franchisee_ID, Franchisee, Job_Count)
	AS
	(SELECT     f.Franchisee_ID, f.Franchisee_Name AS Franchisee, COUNT(j.Job_ID) as Job_Count
		FROM         Tbl_Job_Booking AS j INNER JOIN
							  Tbl_Client AS c ON c.Client_ID = j.Client_ID LEFT OUTER JOIN
							  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
							  Tbl_Users ON j.Job_Designer_ID = Tbl_Users.User_ID
		WHERE   (j.Job_Sold_Date BETWEEN @startDate AND @endDate) AND
				(j.Job_Is_Active = 1) AND 
				(ISNULL(j.Exclude_From_Close_Rate, 0) = 0) AND (ISNULL(j.Is_Deleted, 0) = 0)
		GROUP BY f.Franchisee_ID, f.Franchisee_Name )

SELECT Franchisee, Job_Count, 
--Add Self_Generated_Count to CTE NewJobs set above
		(SELECT     COUNT(j.Job_ID) AS Self_Gen_Count
		FROM         Tbl_Job_Booking AS j INNER JOIN
							  Tbl_Client AS c ON c.Client_ID = j.Client_ID LEFT OUTER JOIN
							  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
							  Tbl_Users ON j.Job_Designer_ID = Tbl_Users.User_ID
		WHERE   (j.Job_Sold_Date BETWEEN @startDate AND @endDate) AND 
				(j.Job_Is_Active = 1) AND 
				(c.Client_ID NOT IN (SELECT     Client_ID
									FROM          Tbl_Calendar AS c1
									WHERE      (ISNULL(Cal_Cancelled, 0) = 0) AND 
				(c.Client_ID = Client_ID))) AND (ISNULL(j.Is_Deleted, 0) = 0) AND
				c.Franchisee_ID= NewJobs.Franchisee_ID)
AS Self_Gen_Count,
--SubQuery to get Call Centre Calls	
				(SELECT     COUNT(cal.Calendar_ID) AS Call_Center_Count
				FROM         Tbl_Calendar AS cal INNER JOIN
									  Tbl_Client AS c ON cal.Client_ID = c.Client_ID LEFT OUTER JOIN
									  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
									  Tbl_Users ON cal.User_ID = Tbl_Users.User_ID
				WHERE     cal.Cal_Start_Date BETWEEN @startDate AND @endDate AND
						  (cal.Cal_Created_By IN
							   (SELECT User_ID FROM Tbl_Users AS Tbl_Users_1 WHERE User_Call_Centre_Rep = 1)) AND
						  c.Franchisee_ID=NewJobs.Franchisee_ID)
AS Call_Centre_Count,
--THIS SECTION ADDS THE TWO PREVIOUIS COLUMNS - IS THERE ANYWAY THAT I can add the 2 colums Call_Centre_Count + SelfGent Count instead of the tedious way I have done below - i.e.
--adding the two select statements....
--Add Self_Generated_Count to CTE NewJobs set above
		(SELECT     COUNT(j.Job_ID) AS Self_Gen_Count
		FROM         Tbl_Job_Booking AS j INNER JOIN
							  Tbl_Client AS c ON c.Client_ID = j.Client_ID LEFT OUTER JOIN
							  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
							  Tbl_Users ON j.Job_Designer_ID = Tbl_Users.User_ID
		WHERE   (j.Job_Sold_Date BETWEEN @startDate AND @endDate) AND 
				(j.Job_Is_Active = 1) AND 
				(c.Client_ID NOT IN (SELECT     Client_ID
									FROM          Tbl_Calendar AS c1
									WHERE      (ISNULL(Cal_Cancelled, 0) = 0) AND 
				(c.Client_ID = Client_ID))) AND (ISNULL(j.Is_Deleted, 0) = 0) AND
				c.Franchisee_ID= NewJobs.Franchisee_ID)
+
--SubQuery to get Call Centre Calls	
				(SELECT     COUNT(cal.Calendar_ID) AS Call_Center_Count
				FROM         Tbl_Calendar AS cal INNER JOIN
									  Tbl_Client AS c ON cal.Client_ID = c.Client_ID LEFT OUTER JOIN
									  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
									  Tbl_Users ON cal.User_ID = Tbl_Users.User_ID
				WHERE     cal.Cal_Start_Date BETWEEN @startDate AND @endDate AND
						  (cal.Cal_Created_By IN
							   (SELECT User_ID FROM Tbl_Users AS Tbl_Users_1 WHERE User_Call_Centre_Rep = 1)) AND
						  c.Franchisee_ID=NewJobs.Franchisee_ID)
AS Appointments

FROM NewJobs

END

Open in new window

0
RBS
Asked:
RBS
1 Solution
 
SharathData EngineerCommented:
You can create another CTE.
ALTER PROCEDURE [dbo].[_CloseRate_Top5_Get]
@startDate DateTime,
@endDate DateTime

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    WITH NewJobs(Franchisee_ID, Franchisee, Job_Count)
	AS
	(SELECT     f.Franchisee_ID, f.Franchisee_Name AS Franchisee, COUNT(j.Job_ID) as Job_Count
		FROM         Tbl_Job_Booking AS j INNER JOIN
							  Tbl_Client AS c ON c.Client_ID = j.Client_ID LEFT OUTER JOIN
							  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
							  Tbl_Users ON j.Job_Designer_ID = Tbl_Users.User_ID
		WHERE   (j.Job_Sold_Date BETWEEN @startDate AND @endDate) AND
				(j.Job_Is_Active = 1) AND 
				(ISNULL(j.Exclude_From_Close_Rate, 0) = 0) AND (ISNULL(j.Is_Deleted, 0) = 0)
		GROUP BY f.Franchisee_ID, f.Franchisee_Name ),
		CTE2 as (

SELECT Franchisee, Job_Count, 
--Add Self_Generated_Count to CTE NewJobs set above
		(SELECT     COUNT(j.Job_ID) AS Self_Gen_Count
		FROM         Tbl_Job_Booking AS j INNER JOIN
							  Tbl_Client AS c ON c.Client_ID = j.Client_ID LEFT OUTER JOIN
							  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
							  Tbl_Users ON j.Job_Designer_ID = Tbl_Users.User_ID
		WHERE   (j.Job_Sold_Date BETWEEN @startDate AND @endDate) AND 
				(j.Job_Is_Active = 1) AND 
				(c.Client_ID NOT IN (SELECT     Client_ID
									FROM          Tbl_Calendar AS c1
									WHERE      (ISNULL(Cal_Cancelled, 0) = 0) AND 
				(c.Client_ID = Client_ID))) AND (ISNULL(j.Is_Deleted, 0) = 0) AND
				c.Franchisee_ID= NewJobs.Franchisee_ID)
AS Self_Gen_Count,
--SubQuery to get Call Centre Calls	
				(SELECT     COUNT(cal.Calendar_ID) AS Call_Center_Count
				FROM         Tbl_Calendar AS cal INNER JOIN
									  Tbl_Client AS c ON cal.Client_ID = c.Client_ID LEFT OUTER JOIN
									  Tbl_Franchisee AS f ON c.Franchisee_ID = f.Franchisee_ID LEFT OUTER JOIN
									  Tbl_Users ON cal.User_ID = Tbl_Users.User_ID
				WHERE     cal.Cal_Start_Date BETWEEN @startDate AND @endDate AND
						  (cal.Cal_Created_By IN
							   (SELECT User_ID FROM Tbl_Users AS Tbl_Users_1 WHERE User_Call_Centre_Rep = 1)) AND
						  c.Franchisee_ID=NewJobs.Franchisee_ID)
AS Call_Centre_Count from NewJobs)

--THIS SECTION ADDS THE TWO PREVIOUIS COLUMNS - IS THERE ANYWAY THAT I can add the 2 colums Call_Centre_Count + SelfGent Count instead of the tedious way I have done below - i.e.
--adding the two select statements....
--Add Self_Generated_Count to CTE NewJobs set above
SELECT Franchisee, Job_Count,Self_Gen_Count+Call_Centre_Count AS Appointments
  FROM CTE2

END

Open in new window

0
 
RBSAuthor Commented:
Perfect - thanks!

rbs
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now