This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

ss TopWaitsforFile SQL

FormerMember
FormerMember

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