IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopSQLforFile') = 1
DROP PROC TopSQLforFile
GO
-- TopSQLforFile PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@FILEID';
-- EXAMPLE:
--
-- EXEC TopSQLforFile '2008-12-23 00:00','2008-12-31 00:00','WT10A_DEMO','1'
CREATE PROC TopSQLforFile
(@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 ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME, SUM(SW.QP/100) TIMESECS ' +
'FROM CONSW_' + @DBID + ' SW INNER JOIN CONF_' + @DBID + ' f ON sw.kxpi = f.orafilenum ' +
'LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGINDATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @ENDDATE + '''' + ', 101) ' +
'AND F.ID = CAST(' + '''' + @FILEID + '''' + ' AS DECIMAL) ' +
'GROUP BY ISNULL(N.NAME, SW.IZHO) ' +
'ORDER BY SUM(SW.QP/100) DESC'
EXEC (@SQL)
END
GO
