IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TPS_interval') = 1
DROP PROC TPS_interval
GO
-- TPS_interval PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME'
-- EXAMPLE:
--
-- EXEC TPS_interval '2008-12-28 23:00', '2008-12-28 23:59','WT10A_DEMO'
CREATE PROC TPS_interval
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@SECS VARCHAR(400),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SECS = 'DATEDIFF(SS,CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101),' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101))'
SELECT @SQL = 'SELECT CONVERT(CHAR(10),DATEHOUR, 101) DAYS, ' +
'SUM(CAST(TIMESECS AS DECIMAL)) TOTAL_WAIT_TIME, ' +
'SUM(EXECS) TRANSACTIONS, ' +
'ROUND(SUM(EXECS)/CAST(' + @SECS + ' AS DECIMAL),2) TRANS_PER_SEC, ' +
'SUM(ROW_COUNT) ROW_COUNT ' +
'FROM CON_STATS_SUM_' + @DBID +
' WHERE DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY CONVERT(CHAR(10),DATEHOUR,101) ' +
'ORDER BY 1'
EXEC (@SQL)
END
GO