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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rbs