Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding Columns derived by Common Table Expressions

Posted on 2011-05-11
2
Medium Priority
?
198 Views
Last Modified: 2012-05-11
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
Comment
Question by:RBS
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35740199
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
 

Author Closing Comment

by:RBS
ID: 35747765
Perfect - thanks!

rbs
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question