Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with creating a stored procedure 1

Status
Not open for further replies.
safari7391

safari7391

Programmer
Apr 29, 2003
33
US
Stored Procedures
SQL Server 2000

I am new to stored procedures, but need to create a stored procedure to increase the performance of a report.
Is it possible to take the following 3 views and place them in a stored procedure? If so how?
View3 reference view2, view2 reference view1 and view1 reference 2 table and 1 view.

(View1)
CREATE VIEW dbo._Daily_Metrics
AS
SELECT TOP 100 PERCENT dbo.dagent.row_date, dbo._Agt_Info.item_type, dbo._Agt_Info.Agt_Name_sy AS Agt_Name, dbo._Agt_Info.Log_ID_sy AS logid,
dbo.dagent.split, dbo._Agt_Info.Sprvr_Name, dbo._Agt_Info.Mgr_Name, dbo._Agt_Info.Grp_Mgr_Name, dbo._Agt_Info.Dir_Name,
dbo._Agt_Info.Location_Name_sy AS Loc_Name, dbo.dagent.acd, dbo.dagent.acdcalls, dbo.dagent.acdtime,
dbo.dagent.acwtime + dbo.dagent.acdtime + dbo.dagent.holdtime AS handle_time, dbo.dagent.acwtime, dbo.dagent.holdtime,
dbo.dagent.acwoutcalls + dbo.dagent.auxoutcalls AS outbound_calls, dbo.dagent.acwouttime + dbo.dagent.auxouttime AS outbound_call_time,
dbo.dagent.ti_auxtime, dbo.dagent.ti_stafftime, s53f.Call_Grouping.Segment, s53f.Call_Grouping.Market, s53f.Call_Grouping.Call_Type
FROM dbo.dagent LEFT OUTER JOIN
s53f.Call_Grouping ON dbo.dagent.split = s53f.Call_Grouping.Grp_Nbr RIGHT OUTER JOIN
dbo._Agt_Info ON dbo.dagent.logid = dbo._Agt_Info.Log_ID_sy
WHERE (dbo.dagent.split IN ('499', '120', '177', '12', '33', '54', '15', '14', '11', '13', '233', '231', '230', '123', '155', '287', '50', '73', '18', '20', '47', '51', '52', '53',
'60', '70', '2', '72', '101', '103', '110', '71', '289', '513'))

(View2)
CREATE VIEW dbo.Agt_DlyChDat_CalTyp_Sum
AS
SELECT TOP 100 PERCENT row_date, Agt_Name, logid, split, Sprvr_Name, Mgr_Name, Grp_Mgr_Name, Dir_Name, Loc_Name, acd, SUM(acdcalls)
AS Sum_acdcalls, SUM(acdtime) AS Sum_acdtime, SUM(handle_time) AS Sum_handle_time, SUM(acwtime) AS Sum_acwtime, SUM(holdtime)
AS Sum_holdtime, SUM(outbound_calls) AS Sum_outbound_calls, SUM(outbound_call_time) AS Sum_outbound_call_time, SUM(ti_auxtime)
AS Sum_ti_auxtime, SUM(ti_stafftime) AS Sum_ti_stafftime, Segment, Market, Call_Type
FROM dbo._Daily_Metrics
GROUP BY row_date, Agt_Name, logid, split, Sprvr_Name, Mgr_Name, Grp_Mgr_Name, Dir_Name, Loc_Name, acd, Segment, Market, Call_Type

(View3)
CREATE VIEW dbo.Agt_DlyChDat_CalTyp_Sum_Avg
AS
SELECT TOP 100 PERCENT row_date, Agt_Name, logid, split, Sprvr_Name, Mgr_Name, Grp_Mgr_Name, Dir_Name, Loc_Name, Sum_acdcalls, Sum_acdtime,
CASE WHEN [sum_acdtime] = 0 OR
[sum_acdcalls] = 0 THEN 0 ELSE (Sum_acdtime / Sum_acdcalls) END AS Avg_Talk_Time, Sum_handle_time, CASE WHEN [Sum_handle_time] = 0 OR
[sum_acdcalls] = 0 THEN 0 ELSE (Sum_handle_time / Sum_acdcalls) END AS Avg_Handled_Time, Sum_acwtime, CASE WHEN [Sum_acwtime] = 0 OR
[sum_acdcalls] = 0 THEN 0 ELSE (Sum_acwtime / Sum_acdcalls) END AS Avg_Call_Work_Time, Sum_holdtime, Sum_outbound_calls,
Sum_outbound_call_time, Sum_ti_auxtime, Sum_ti_stafftime, Segment, Market, Call_Type
FROM dbo.Agt_DlyChDat_CalTyp_Sum
GROUP BY row_date, Agt_Name, logid, split, Sprvr_Name, Mgr_Name, Grp_Mgr_Name, Dir_Name, Loc_Name, Sum_acdcalls, Sum_acdtime, Sum_handle_time,
Sum_acwtime, Sum_holdtime, Sum_outbound_calls, Sum_outbound_call_time, Sum_ti_auxtime, Sum_ti_stafftime, Segment, Market, Call_Type
ORDER BY row_date DESC
 
Sort by date Sort by votes
Read the topic "Creating an Indexing View" in Books Online. It's good stuff and should help your view performance noticeably.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK
-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.
 
Upvote 0 Downvote
Try it this way:

Code: 
CREATE PROCEDURE your_proc
  
  AS

	SELECT  dbo.dagent.row_date, 
			dbo._Agt_Info.Agt_Name_sy AS Agt_Name, 
			dbo._Agt_Info.Log_ID_sy AS logid,
            dbo.dagent.split, 
			dbo._Agt_Info.Sprvr_Name, 	
			dbo._Agt_Info.Mgr_Name, 
			dbo._Agt_Info.Grp_Mgr_Name,
			dbo._Agt_Info.Dir_Name,
            dbo._Agt_Info.Location_Name_sy AS Loc_Name, 
			SUM( dbo.dagent.acdcalls ) AS Sum_acdcalls, 
			SUM( dbo.dagent.acdtime) AS Sum_acdtime, 
	        CASE WHEN SUM( dbo.dagent.acdcalls ) = 0 THEN 0 ELSE ( SUM( dbo.dagent.acdtime) / SUM( dbo.dagent.acdcalls ) ) END AS Avg_Talk_Time, 
            SUM( dbo.dagent.acwtime + dbo.dagent.acdtime + dbo.dagent.holdtime ) AS Sum_handle_time, 
			CASE WHEN SUM( dbo.dagent.acdcalls ) = 0 THEN 0 ELSE ( SUM( dbo.dagent.acwtime + dbo.dagent.acdtime + dbo.dagent.holdtime ) /  SUM( dbo.dagent.acdcalls ) ) END AS Avg_Handled_Time, 
			SUM( dbo.dagent.acwtime ) AS Sum_acwtime, 
			CASE WHEN SUM( dbo.dagent.acdcalls ) = 0 THEN 0 ELSE ( SUM( dbo.dagent.acwtime ) / SUM( dbo.dagent.acdcalls ) ) END AS Avg_Call_Work_Time, 
			SUM( dbo.dagent.holdtime ) AS Sum_holdtime, 
            SUM( dbo.dagent.acwoutcalls + dbo.dagent.auxoutcalls ) AS Sum_outbound_calls, 
			SUM( dbo.dagent.acwouttime + dbo.dagent.auxouttime ) AS Sum_outbound_call_time,
            SUM( dbo.dagent.ti_auxtime ) AS Sum_ti_auxtime, 
			SUM( dbo.dagent.ti_stafftime ) AS Sum_ti_stafftime, 
			s53f.Call_Grouping.Segment, 
			s53f.Call_Grouping.Market, 
			s53f.Call_Grouping.Call_Type
		FROM dbo.dagent 
			LEFT OUTER JOIN s53f.Call_Grouping ON dbo.dagent.split = s53f.Call_Grouping.Grp_Nbr 
			RIGHT OUTER JOIN dbo._Agt_Info ON dbo.dagent.logid = dbo._Agt_Info.Log_ID_sy
		WHERE dbo.dagent.split IN ('499', '120', '177', '12', '33', '54', '15', '14', '11', '13', '233', '231', '230', '123', '155', '287', '50', '73', '18', '20', '47', '51', '52', '53',
		                      		'60', '70', '2', '72', '101', '103', '110', '71', '289', '513'
								  )
		GROUP BY row_date, Agt_Name, logid, split, Sprvr_Name, Mgr_Name, Grp_Mgr_Name, Dir_Name, Loc_Name, acd, Segment, Market, Call_Type
		ORDER BY row_date DESC

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
For some reason the rows are off by 347. With your stored proc I have a total of 588007 compared to my views which give me 587660. Any idea why?
 
Upvote 0 Downvote
Hmmm, It is because of column 'acd'

In your View 2 you are grouping data by this column, than in View 3 you are grouping data by all totals calculated in View 2.

You can try to remove 'acd' column from GROUP BY clause of my previous post, but it will not be exactly the same query as your views - you may get multiplied results in SUM columns

So try it and post there what you get

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
Removing the acd gives the store proc 586840 rows to the views 587660 rows.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
202
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
506
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
623
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
361
gmmastros
gmmastros
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
196
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top