Link to home
Start Free TrialLog in
Avatar of RBS
RBS

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RBS
RBS

ASKER

Perfect - thanks!

rbs