Description
This procedure, when executed, will return all Programs for the given date range ordered by day and wait time. This is similar to the Programs tab within the Ignite GUI. See comments inside the procedure for more details on the parameters and methods for calling. Install the stored procedure into the Ignite repository database.
Stored Procedure
IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopProgramByDay') = 1
DROP PROC TopProgramByDay
GO
-- TopProgramByDay PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@INSTANCE_NAME';
-- EXAMPLE:
--
-- EXEC TopProgramByDay '2009-02-07 00:00', '2009-03-07 23:59', 'OKPRD160'
CREATE PROC TopProgramByDay
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @INSTANCE_NAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @INSTANCE_NAME
SELECT @SQL = 'SELECT DAY, PROGRAM_NAME, TIMESECS FROM ( ' +
' SELECT CONVERT(VARCHAR(10), SW.D, 101) DAY, PR.NAME PROGRAM_NAME, SUM(SW.QP)/100 TIMESECS ' +
' FROM CONSW_' + @DBID + ' SW INNER JOIN CONPR_' + @DBID + ' PR ON SW.UDPW = PR.ID ' +
' WHERE SW.D BETWEEN CONVERT(DATETIME, '''+@FROM_DATE+';'', 101) AND CONVERT(DATETIME, '''+@TO_DATE+';'', 101) ' +
' GROUP BY CONVERT(VARCHAR(10), SW.D, 101), PR.NAME) TOT ' +
'ORDER BY DAY, TIMESECS DESC'
EXEC (@SQL)
END
GO
