-- Stored Procedure: dbo.DbActivityTrend
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DbActivityTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbActivityTrend]
GO


CREATE PROCEDURE [dbo].[DbActivityTrend] 
	@DbName varchar(30) = ''
	  
AS
/*******************************************************************************************************
*	admin.dbo.DbActivityTrend 
*	Creator:	Bill Wunder
*	Date: 		2-19-2001
*	Outline:	Query the stored samples from master..sysperfinfo
*			to display the transaction rate for a database
*			
*			
*	How it Works:	The big picture
*			
*	usage:
*	EXECUTE admin.dbo.DbActivityTrend customer
*	Notes:		'any additional comments'
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- set session
SET NOCOUNT ON


-- create temp tables


-- body of stored procedure
select p1.dbName,
	cast((cast(p1.LastTranRateSample as numeric(12,2)) - cast(p1.FirstTranRateSample as numeric(12,2)))/cast(datediff(s, p1.FirstSampleDt, p1.LastSampleDt) as numeric(12,2)) as numeric(12,2)) as avgTxnPerSec, 
	cast(p1.MinTxnPerSec as numeric(12,2)) as MinTxnPerSec, 
	cast(p1.MaxTxnPerSec as numeric(12,2)) as MaxTxnPerSec, 
	cast(cast(p1.TransActivityDetected as numeric(12,2))/cast(p2.TransActivityDetected as numeric(12,2)) * 100 as int) as PctActive,
	convert(varchar(21),p1.SampleDate,102) as [Date],
	datepart(dw, p1.SampleDate) as [Day of Week]
from admin.dbo.dbperfhistory p1
inner join admin.dbo.dbperfhistory p2
on p1.SampleDate = p2.SampleDate
where p1.DbName like @dbName + '%' 
and p2.DbName = '_Total'
order by p1.dbname, p1.sampledate



RETURN



GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO