IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopWaitsforFile') = 1
DROP PROC TopWaitsforFile
GO
-- TopWaitsforFile PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' , '@FILEID'
-- EXAMPLE:
--
-- EXEC TopWaitsforFile '2008-12-23 00:00','2008-12-31 00:00','WT10A_DEMO','1'
CREATE PROC TopWaitsforFile
(@BEGINDATE VARCHAR(16), @ENDDATE VARCHAR(16), @DBNAME VARCHAR(50), @FILEID VARCHAR(5))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT EV.NAME, SUM(SW.QP/100) TIMESECS ' +
'FROM CONSW_' + @DBID + ' SW INNER JOIN CONF_' + @DBID + ' F ON SW.KXPI = F.ORAFILENUM ' +
'INNER JOIN CONEV_' + @DBID + ' EV ON SW.KEEQ = EV.ID ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGINDATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @ENDDATE + '''' + ', 101) ' +
'AND F.ID = CAST(' + '''' + @FILEID + '''' + ' AS DECIMAL) ' +
'GROUP BY EV.NAME ' +
'ORDER BY SUM(SW.QP/100) DESC '
EXEC (@SQL)
END
GO