query.sql

SELECT HOLD.TARGETFINISH, HOLD.TICKETID, HOLD.ACTUALSTART, HOLD.ACTUALFINISH, TO_CHAR(TO_DATE(HOLD.ACTUALSTART,'yyyy-MM-dd HH24:MI'),'YYYY-IW') as START_WEEK, TO_CHAR(TO_DATE(HOLD.ACTUALFINISH,'yyyy-MM-dd HH24:MI'),'YYYY-IW') as END_WEEK, HOLD.RESOLVER, HOLD.RESOLVEDBY, HOLD.CLASSIFICATIONID, HOLD.PRIORITY, HOLD.INDICATEDPRIORITY1, HOLD.RESPONSE_TIME, HOLD.RESOLUTION_TIME, HOLD.HOLD_DUR, HOLD.EXPECTED_RESOLUTION, INPROG.INPROG_DUR as INPROG_SERVICE_DESK_DUR, CASE 
WHEN HOLD.RESOLUTION_TIME > HOLD.EXPECTED_RESOLUTION THEN 'NO'
ELSE 'YES'
END as SLA
FROM (
	SELECT C.TARGETFINISH, C.TICKETID, TO_CHAR(C.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, C.ACTUALFINISH, C.RESOLVER, C.RESOLVEDBY, C.CLASSIFICATIONID, C.PRIORITY, C.INDICATEDPRIORITY1, C.RESPONSE_TIME, C.RESOLUTION_TIME, CASE 
	WHEN D.HOLD_DUR is null THEN 0 
	ELSE D.HOLD_DUR 
	END as HOLD_DUR, C.EXPECTED_resolution
	FROM (
		SELECT A.TARGETFINISH, A.TICKETID, A.ACTUALSTART, A.ACTUALFINISH, A.RESOLVER, A.RESOLVEDBY, A.CLASSIFICATIONID, A.Priority, A.Indicatedpriority1, A.RESPONSE_TIME, B.RESOLUTION_TIME, A.EXPECTED_resolution
		FROM (
			SELECT X.TARGETFINISH, TICKETID, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, ROUND((HELDESK_MIN_TIME-reportdate)*24,2) as RESPONSE_TIME, SLACOMMITMENTS.VALUE as EXPECTED_resolution
			FROM(
				(SELECT TARGETFINISH, TICKETID, REPORTDATE, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, MIN(CHANGEDATE) as HELDESK_MIN_TIME
				FROM (
					SELECT INC.TARGETFINISH, INC.TICKETID, REPORTDATE, INC.ACTUALSTART, TO_CHAR(INC.ACTUALFINISH,'YYYY-MM-DD HH24:MI') as ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, TK.CHANGEDATE
					FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
					WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') AND tk.ASSIGNEDOWNERGROUP IN ('HELPDESK.HUN','SD.ITSUPPORT.HUN','USERADMIN.HUN') AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM')
				)
				GROUP BY TICKETID,REPORTDATE,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1)
			) X, MAXIMO.AFFECTEDSLAS, MAXIMO.SLACOMMITMENTS
			WHERE AFFECTEDSLAS.fordelete='NO' 
			AND SLACOMMITMENTS.slanum=AFFECTEDSLAS.genericslaid 
			AND X.TICKETID=AFFECTEDSLAS.objectkey
		)A, 
		(
			SELECT TICKETID, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, ROUND((ACTUALFINISH-HELDESK_MIN_TIME)*24,2) as RESOLUTION_TIME
			FROM(
				(SELECT TICKETID, REPORTDATE, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, MIN(CHANGEDATE) as HELDESK_MIN_TIME
				FROM (
					SELECT INC.TICKETID, REPORTDATE, TO_CHAR(INC.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, INC.ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, TK.CHANGEDATE
					FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
					WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') AND tk.ASSIGNEDOWNERGROUP IN ('HELPDESK.HUN','SD.ITSUPPORT.HUN','USERADMIN.HUN') AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM'))
					GROUP BY TICKETID,REPORTDATE,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1
				)
			)
		)B
		WHERE a.TICKETID=B.TICKETID
	) C 
	LEFT JOIN (
		SELECT TICKETID, SUM(HOLD_DUR) as HOLD_DUR, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, STATUS
		FROM (
			SELECT INC.TICKETID, TO_CHAR(INC.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, TO_CHAR(INC.ACTUALFINISH,'YYYY-MM-DD HH24:MI') as ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, (round(substr( TK.STATUSTRACKING,1,instr(TK.STATUSTRACKING, ':', 1,1)-1)+  (substr( TK.STATUSTRACKING,instr(TK.STATUSTRACKING, ':', 1,1)+1, instr(TK.STATUSTRACKING, ':',1,2) - instr(TK.STATUSTRACKING, ':',1,1)-1 ) )/60,2)) as HOLD_DUR
			FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
			WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') AND TK.STATUS LIKE '%HOLD%' --AND TO_CHAR(INC.ACTUALSTART,'YYYY-MM')>='2018-03'
			AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM')
		)
		GROUP BY TICKETID,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1,STATUS
	)D ON c.TICKETID=d.ticketid order by ACTUALSTART ASC
) HOLD 
LEFT JOIN (
	SELECT C.TICKETID, TO_CHAR(C.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, C.ACTUALFINISH, C.RESOLVER, C.RESOLVEDBY, C.CLASSIFICATIONID, C.PRIORITY, C.INDICATEDPRIORITY1, C.RESPONSE_TIME, C.RESOLUTION_TIME, CASE 
	WHEN D.INPROG_DUR is null THEN 0 
	ELSE D.INPROG_DUR 
	END as INPROG_DUR, C.EXPECTED_resolution
	FROM (
		SELECT A.TICKETID, A.ACTUALSTART, A.ACTUALFINISH, A.RESOLVER, A.RESOLVEDBY, A.CLASSIFICATIONID, A.Priority, A.Indicatedpriority1, A.RESPONSE_TIME, B.RESOLUTION_TIME, A.EXPECTED_resolution
		FROM (
			SELECT TICKETID, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, ROUND((HELDESK_MIN_TIME-reportdate)*24,2) as RESPONSE_TIME, SLACOMMITMENTS.VALUE as EXPECTED_resolution
			FROM(
				(SELECT TICKETID, REPORTDATE, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, MIN(CHANGEDATE) as HELDESK_MIN_TIME
				FROM (
				SELECT INC.TICKETID, REPORTDATE, INC.ACTUALSTART, TO_CHAR(INC.ACTUALFINISH,'YYYY-MM-DD HH24:MI') as ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, TK.CHANGEDATE
				FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
				WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') AND tk.ASSIGNEDOWNERGROUP IN ('HELPDESK.HUN','SD.ITSUPPORT.HUN','USERADMIN.HUN') AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM'))
				GROUP BY TICKETID,REPORTDATE,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1)
			) X, MAXIMO.AFFECTEDSLAS, MAXIMO.SLACOMMITMENTS
			WHERE AFFECTEDSLAS.fordelete='NO' 
			AND SLACOMMITMENTS.slanum=AFFECTEDSLAS.genericslaid 
			AND X.TICKETID=AFFECTEDSLAS.objectkey
		)A, 
		(
			SELECT TICKETID, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, ROUND((ACTUALFINISH-HELDESK_MIN_TIME)*24,2) as RESOLUTION_TIME
			FROM(
				(SELECT TICKETID, REPORTDATE, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1, MIN(CHANGEDATE) as HELDESK_MIN_TIME
				FROM (
				SELECT INC.TICKETID, REPORTDATE, TO_CHAR(INC.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, INC.ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, TK.CHANGEDATE
				FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
				WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') AND tk.ASSIGNEDOWNERGROUP IN ('HELPDESK.HUN','SD.ITSUPPORT.HUN','USERADMIN.HUN') AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM'))
				GROUP BY TICKETID,REPORTDATE,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1))
		)B
		WHERE a.TICKETID=B.TICKETID
	) C 
	LEFT JOIN (
		SELECT TICKETID, SUM(INPROG_DUR) as INPROG_DUR, ACTUALSTART, ACTUALFINISH, RESOLVER, RESOLVEDBY, CLASSIFICATIONID, Priority, Indicatedpriority1
		FROM (
			SELECT INC.TICKETID, TO_CHAR(INC.ACTUALSTART,'YYYY-MM-DD HH24:MI') as ACTUALSTART, TO_CHAR(INC.ACTUALFINISH,'YYYY-MM-DD HH24:MI') as ACTUALFINISH, INC.RESOLVER, INC.RESOLVEDBY, INC.CLASSIFICATIONID, INC.IMPACT+INC.URGENCY-1 as Priority, INC.Indicatedpriority1, TK.STATUS, (round(substr( TK.STATUSTRACKING,1,instr(TK.STATUSTRACKING, ':', 1,1)-1)+  (substr( TK.STATUSTRACKING,instr(TK.STATUSTRACKING, ':', 1,1)+1, instr(TK.STATUSTRACKING, ':',1,2) - instr(TK.STATUSTRACKING, ':',1,1)-1 ) )/60,2)) as INPROG_DUR
			FROM MAXIMO.TICKET INC LEFT JOIN MAXIMO.TKSTATUS TK ON INC.TICKETID=TK.TICKETID
			WHERE INC.TICKETID LIKE 'SR%' AND INC.STATUS IN ('CLOSED','RESOLVED','TOBECLOSED') AND INC.CLASSIFICATIONID LIKE 'SELF%' AND INC.IMPACT+INC.URGENCY-1 IN ('5','6','7') and TK.assignedownergroup IN ('HELPDESK.HUN','SD.ITSUPPORT.HUN') AND (TK.STATUS LIKE '%INPROG%' OR TK.STATUS LIKE '%QUEUED%') --AND TO_CHAR(INC.ACTUALSTART,'YYYY-MM')>='2018-03'
			AND TO_CHAR(INC.ACTUALFINISH,'YYYY-MM')= TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM')
		)
		GROUP BY TICKETID,ACTUALSTART,ACTUALFINISH,RESOLVER,RESOLVEDBY,CLASSIFICATIONID,Priority,Indicatedpriority1
	)D ON c.TICKETID=d.ticketid
	order by ACTUALSTART ASC
) INPROG ON HOLD.TICKETID=INPROG.TICKETID