IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'SqlCountbyProgramMachine') = 1
DROP PROC SqlCountbyProgramMachine
GO
-- SqlCountbyProgramMachine
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' ,
-- EXAMPLE:
--
-- EXEC SqlCountbyProgram '2008-12-13 12:00', '2008-12-24 12:00', 'WT10A_DEMO', 'sqlplus.exe'
CREATE PROC SqlCountbyProgramMachine
(@FROM_DATE VARCHAR(25), @TO_DATE VARCHAR(25), @DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT SM.NAME as Machine ,PR.NAME as Program, SUM(SS.EXECS) as Executions, CONVERT(VARCHAR,SS.D,101)as RunDate ' +
'FROM CONSW_' + @DBID + ' SW ' +
' INNER JOIN CONPR_' + @DBID + ' PR on SW.UDPW = PR.ID ' +
' INNER JOIN CONSS_' + @DBID + ' SS ON SS.H = SW.IZHO ' +
' INNER JOIN CONM_' + @DBID + ' SM ON SW.PWMY = SM.ID ' +
' WHERE SW.D BETWEEN CONVERT(DATETIME, '''+@FROM_DATE+''', 101) AND CONVERT(DATETIME, '''+@TO_DATE+''', 101) ' +
' and SS.D BETWEEN CONVERT(DATETIME, '''+@FROM_DATE+''', 101) AND CONVERT(DATETIME, '''+@TO_DATE+''', 101) ' +
' GROUP BY PR.NAME, SM.NAME, CONVERT(VARCHAR,SS.D,101) ' +
'ORDER BY PR.NAME'
EXEC (@SQL)
END
go
