SQL Server:
SELECT YEAR(BEGINDATE) AS Year, MONTH(BEGINDATE) AS Month, DAY(BEGINDATE) AS Day, SUM(DATEDIFF(s, BEGINDATE, ENDDATE)) AS SumOfExecutionInSeconds
FROM EXCEPTIONCATALOG
WHERE DATEDIFF(d, BEGINDATE, GETDATE())<=30
GROUP BY YEAR(BEGINDATE), MONTH(BEGINDATE), DAY(BEGINDATE)
ORDER BY YEAR(BEGINDATE) DESC, MONTH(BEGINDATE) DESC, DAY(BEGINDATE) DESC
Oracle:
SELECT EXTRACT(year FROM BEGINDATE) AS Year, EXTRACT(month FROM BEGINDATE) AS Month, EXTRACT(day FROM BEGINDATE) AS Day,
SUM(to_number(to_char(ENDDATE,'sssss'))-to_number(to_char(BEGINDATE,'sssss'))) AS SumOfExecutionInSeconds
FROM EXCEPTIONCATALOG
WHERE BEGINDATE>=SYSDATE-30
GROUP BY EXTRACT(year FROM BEGINDATE), EXTRACT(month FROM BEGINDATE), EXTRACT(day FROM BEGINDATE)
ORDER BY EXTRACT(year FROM BEGINDATE) DESC, EXTRACT(month FROM BEGINDATE) DESC, EXTRACT(day FROM BEGINDATE) DESC
SQL Server or Oracle:
select pg.NAME "ControlEntity", pd.DISPLAYNAME "ControlPoint", pcpd.PERIODYYYYMM "YrMonth", pc.EXECUTIONCOUNT "Executions", pc.RECORDSPROCESSED "Instances", pc.EXECUTIONSECONDS "SumOfExecutionInSeconds" from PROCESSCOUNTS pc
inner join PROCESSCOUNTPD pcpd on pcpd.PROCESSCOUNTPDPK = pc.PROCESSCOUNTPDFK
full outer join PROCESSDEFINITION pd on pd.PROCESSDEFINITIONPK = pc.PROCESSDEFINITIONFK
inner join PROCESSGROUP pg on pg.PROCESSGROUPPK = pd.PROCESSGROUPFK
where pg.PROCESS_TYPE_FK <> 5
order by pg.NAME asc, pd.DISPLAYNAME asc, pcpd.PERIODYYYYMM desc;
Comments
0 comments
Please sign in to leave a comment.