Converting SQL code into stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting SQL code into stored procedure

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 08:54:45

In a previous post I asked for help with creating a temp table and then using it. I was able to get that working BUT unfortunately I found out that Reporting Services does not support creating tables (or dropping them) in SQL run there. So I decided to create a stored procedure which should allow me to do the steps needed. What is happening has me confused though. When creating the Stored Procedure, it appears that the first step of the procedure code is being treated properly but subsequent code pieces are actually being executed instead of being part of creating the stored procedure. My simplified code is:

CREATE PROCEDURE Create_Temp_FirstPass
AS
BEGIN

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '#ttemp')
DROP TABLE #ttemp
GO

CREATE TABLE #ttemp
( Bucket_First_Pass varchar(20),
Count_First_Pass decimal(5,2))
GO

--- INSERT...SELECT
--- Get First CallNumbers
INSERT INTO #ttemp
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS Bucket_First_Pass,
COUNT(ED_Service_Request.Status_) AS Count_First_Pass

FROM ED_Service_Request

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)
AND (ED_Service_Request.C_Escalate_To IS NULL)
AND (ED_Service_Request.C_Transfer_To IS NULL)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)
GO
END

When I execute the create procedure code the table is created and the data is written to it instead of a stored procedure being created.

The following errors are generated:
Msg 102, Level 15, State 1, Procedure Create_Temp_FirstPass, Line 7
Incorrect syntax near '#ttemp'.
Msg 2714, Level 16, State 6, Line 2
There is already an object named '#ttemp' in the database.

(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'END'.

As always, all help is appreciated.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 08:58:23

CREATE PROCEDURE Create_Temp_FirstPass
AS
BEGIN

SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS Bucket_First_Pass,
COUNT(ED_Service_Request.Status_) AS Count_First_Pass

FROM ED_Service_Request

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)
AND (ED_Service_Request.C_Escalate_To IS NULL)
AND (ED_Service_Request.C_Transfer_To IS NULL)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)
GO

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 09:11:30

Madhivanan, Do I not need to drop the table if it exists and then create the table?

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 09:18:47

Madhivanan, I am not sure best way to explain butthese calculations will be used in a series of Unions so I need the #ttemp table to be joined in a subsequent SQL:

--- Get Total
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'5-First Call %' AS Resolution_Type_SORT,
'First Call %' AS Resolution_Type,
ROUND(100*(max(Count_First_Pass)/COUNT(ED_Service_Request.Status_)),2) AS ReportCount

FROM ED_Service_Request

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

Union

SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'2-Escalated' AS Resolution_Type_SORT,
'Escalated' AS Resolution_Type,
COUNT(ED_Service_Request.Status_) AS Count

FROM ED_Service_Request
WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)
AND (ED_Service_Request.C_Escalate_To IS NOT NULL)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

There are 3 more unions to create 3 other types of totals. In the code I omitted them just to keep the post shorter...

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:21:15

You can simply use those unions without using a temp table

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 09:22:46

so the steps are:
1-Create temp value for later use by yyyy/mm,
2-Use temp value and total to create a percentage calc by yyyy/mm,
3-Get count type Excalated records by yyyy/mm,
4-Get count type Transferred records by yyyy/mm
5-Get count Non-Excalated/Transferred records by yyyy/mm
6-Get count ALL records by yyyy/mm

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:30:21

Why do you want to use temp table?

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 09:45:56

I thought that was the only way. The code to create the temp file will be joined in the next step as it creates a value and yyyy/mm buckets for the last 12 months, not just a single value.

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 10:05:34

The #ttemp data returns this for the last 5 months:
Bucket_First_Pass,Count_First_Pass
2009/05, 21
2009/06, 11
2009/07, 19
2009/08, 10
2009/09, 16

And when the unions execute I get:
Date_Bucket, Resolution_Type_SORT, Resolution_Type, Count
2009/05, 1-First Call, First Call, 21
2009/05, 2-Escalated, Escalated, 2
2009/05, 3-Transfered, Transfered, 2
2009/05, 4-Total Incidents, Total Incidents, 25
2009/05, 5-First Call %, First Call %, 84.00
2009/06, 1-First Call, First Call, 11
2009/06, 2-Escalated, Escalated, 6
2009/06, 3-Transfered, Transfered, 4
2009/06, 4-Total Incidents, Total Incidents, 21
2009/06, 5-First Call %, First Call %, 52.38
2009/07, 1-First Call, First Call, 19
2009/07, 2-Escalated, Escalated, 7
2009/07, 3-Transfered, Transfered, 2
2009/07, 4-Total Incidents, Total Incidents, 28
2009/07, 5-First Call %, First Call %, 67.86
2009/08, 1-First Call, First Call, 10
2009/08, 2-Escalated, Escalated, 1
2009/08, 3-Transfered, Transfered, 7
2009/08, 4-Total Incidents, Total Incidents, 18
2009/08, 5-First Call %, First Call %, 55.56
2009/09, 1-First Call, First Call, 16
2009/09, 2-Escalated, Escalated, 4
2009/09, 3-Transfered, Transfered, 6
2009/09, 4-Total Incidents, Total Incidents, 26
2009/09, 5-First Call %, First Call %, 61.54

My problem is that it fails in reporting services because SSRS does not support creating, dropping, and inserting into tables so I am trying to make a stored procedure from the executable code.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 05:57:42

I dont know what you are doing with temp tables
Can you post the full code that causes problem?

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-09 : 08:58:46

This is the full working code. This is supposed to run in reporting services. When I test this WITHOUT being a stored procedure, it functions properly. When I try to use in SSRS I get error that my parameters are not declared. So that is reason for trying to make a Stored Procedure. As for the need for #ttemp, if you can point me in a better direction I will greatly appreciate it. I also appreciate your patience!

CREATE PROCEDURE Create_Call_Calcs_W_ttemp
AS
BEGIN

Declare @ClubArea varchar(80),
@EnteredBy varchar(80),
@TeamLeader varchar(80),
@Dept varchar(80),
@Branch varchar(80),
@Region varchar(80)

--- Create temp table
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '#ttemp')
DROP TABLE #ttemp

CREATE TABLE #ttemp
( Bucket_First_Pass varchar(20),
Count_First_Pass decimal(5,2))

INSERT INTO #ttemp
--- Calc = Count of First_Pass records / Total Number Records
--- First Call = Not Escalated and Not Transfered
--- Get First CallNumbers by YYYY/MM

SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS Bucket_First_Pass,
COUNT(ED_Service_Request.Status_) AS Count_First_Pass

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)
AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea) OR @ClubArea = ' ')
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')
AND (ED_Service_Request.C_Escalate_To IS NULL)
AND (ED_Service_Request.C_Transfer_To IS NULL)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

--- Get Total Half of Calc and Join to #ttemp by YYYY/MM

SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'5-First Call %' AS Resolution_Type_SORT,
'First Call %' AS Resolution_Type,
ROUND(100*(max(Count_First_Pass)/COUNT(ED_Service_Request.Status_)),2) AS Count

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

LEFT OUTER JOIN #ttemp AS ttemp
ON (CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)) = Bucket_First_Pass

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea) OR @ClubArea = ' ')
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

Union

--- Get Count of records Escalated by YYYY/MM
--- Record is Escalated if Not NULL
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'2-Escalated' AS Resolution_Type_SORT,
'Escalated' AS Resolution_Type,
COUNT(ED_Service_Request.Status_) AS Count

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea))
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

AND (ED_Service_Request.C_Escalate_To IS NOT NULL)

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

Union
--- Get Count of records Transfered by YYYY/MM
--- Record is Transfered if Not NULL AND Escalated IS NULL
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'3-Transfered' AS Resolution_Type_SORT,
'Transfered' AS Resolution_Type,
COUNT(ED_Service_Request.Status_) AS Count

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

AND (ED_Service_Request.C_Escalate_To IS NULL)
AND (ED_Service_Request.C_Transfer_To IS NOT NULL)

AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea))
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

Union
--- Get First Call resolved
--- First Call resolved when Transferred AND Escalated are both NULL
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'1-First Call' AS Resolution_Type_SORT,
'First Call' AS Resolution_Type,
COUNT(ED_Service_Request.Status_) AS Count

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

AND (ED_Service_Request.C_Escalate_To IS NULL)
AND (ED_Service_Request.C_Transfer_To IS NULL)

AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea))
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

Union
--- Get total count again
SELECT CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111) AS [Date Bucket],
'4-Total Incidents' AS Resolution_Type_SORT,
'Total Incidents' AS Resolution_Type,
COUNT(ED_Service_Request.Status_) AS Count

FROM ED_Region

RIGHT OUTER JOIN ED_Branch
RIGHT OUTER JOIN ED_C_Department
RIGHT OUTER JOIN ED_C_Club_Area
RIGHT OUTER JOIN ED_Service_Request

ON ED_C_Club_Area.C_Club_Area_Id = ED_Service_Request.C_Club_Area_Id
ON ED_C_Department.C_Department_Id = ED_Service_Request.C_Create_User_Dept_Id
ON ED_Branch.Branch_Id = ED_Service_Request.C_Create_User_Branch_Id
ON ED_Region.Region_Id = ED_Branch.C_Region_Id

LEFT OUTER JOIN ED_C_Employee_Team
LEFT OUTER JOIN ED_Employee AS ED_Employee_TeamLeader
ON ED_C_Employee_Team.On_Team_Of_Id = ED_Employee_TeamLeader.Employee_Id

RIGHT OUTER JOIN ED_Employee ON ED_C_Employee_Team.Employee_Id = ED_Employee.Employee_Id
ON ED_Service_Request.Entered_By = ED_Employee.Employee_Id

WHERE (ED_Service_Request.C_MRTSNum IS NULL)
AND (ED_Service_Request.C_Feedback_Type = 'Complaint')
AND (ED_Service_Request.C_Resolved_Time IS NOT NULL)
AND (ED_Service_Request.Rn_Create_Date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0))
AND (ED_Service_Request.Rn_Create_Date < GETDATE() + 1)

AND (ED_C_Club_Area.Rn_Descriptor IN (@ClubArea))
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

GROUP BY CONVERT(varchar(7), ED_Service_Request.Rn_Create_Date, 111)

END

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 09:16:57

Are the following variables supposed to parameters of the procedure?

Declare @ClubArea varchar(80),
@EnteredBy varchar(80),
@TeamLeader varchar(80),
@Dept varchar(80),
@Branch varchar(80),
@Region varchar(80)

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-09 : 11:53:32

Yes they are filters the user will select from to get the proper data for their area.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:26:37

Then your procedure should start with

CREATE PROCEDURE Create_Call_Calcs_W_ttemp
(
@ClubArea varchar(80),
@EnteredBy varchar(80),
@TeamLeader varchar(80),
@Dept varchar(80),
@Branch varchar(80),
@Region varchar(80)
)
AS
BEGIN
.
.
.

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-10 : 07:44:24

I obviously didn't see that. I have the declares in example at 8:58 on 9/9 but not on the Create line. I will try. Thanks!

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources