/*end*/ with wost (wonum ,status , ed , st) as (select wonum ,case when status='WMAT' then 'APPR' else status end   ,max(CHANGEDATE) ed ,min(CHANGEDATE) st from maximo.WOSTATUS  where  ((status='COMP' and CHANGEDATE >   '2016-04-01' ) or (status in ('APPR','WMAT') and CHANGEDATE <  '2016-05-01') or status='CONTR' ) and parent is not null group by wonum , case when status='WMAT' then 'APPR' else status end ),  /*end*/
 /*cat*/ srst (TICKETID ,status , ed , st) as  (select TICKETID, status   ,max(CHANGEDATE) ed ,min(CHANGEDATE) st from maximo.TKSTATUS  where   ((status='CLOSED' and CHANGEDATE >   '2016-04-01' ) or (status = 'ASSIGNED' and CHANGEDATE <  '2016-05-01') or status='RESOLVED' )  group by TICKETID , status),  /*end*/
 /*cat*/ WO  (wonum , status,metod, CG ,CC,PG, PRED, st_t,  ed_t, t_comp,PMDUEDATE, st_alarm, ed_alarm , alarm_time , norma, alarm_norm , is_reg,is_close)as  /*end*/
 /*cat*/ (select  wo.wonum ,wo.status ,L_PM.SHORTPATH, WO.COMMODITYGROUP,WO.COMMODITY,WO.PERSONGROUP, WO.PRED, w2.st,  w3.ed  ,w1.ed,PMDUEDATE  /*end*/
 /*cat*/ ,/* st_alarm время начала нарушения в отчетном периоде расчитывается для плановых нарад заказов от плановой даты для внеплановых от даты согласования  , выполняется корекция с учетом начала периода, и проверкой ST_t+norma > больше начала преиода */  case      when wo.PMNUM is null     then 	case  when w2.st + 	case when 	case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24  else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour < '2016-04-01'      			  then  '2016-04-01' 				  else w2.st +  case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  		    end     else   case when case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  < '2016-04-01' then '2016-04-01' else case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end+ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour   end  end  st_alarm   /*end*/
 /*cat*/ ,/* ed_alarm время конца нарушения в отчетном периоде*/case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end   ed_alarm   /*end*/
 /*cat*/ ,/* просрочка в часах  если  st_alarm <  ed_alarm то alarm_time = ed_alarm-  st_alarm иначе alarm_time=0 */ case when  /*end*/
 /*cat*/ case when wo.PMNUM is null then case  when w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour end else  case when case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  < '2016-04-01' then '2016-04-01' else case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end+ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour   end  end   <=  /*end*/
 /*cat*/ case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end   /*end*/
 /*cat*/ then   TIMESTAMPDIFF( 2,CHAR(timestamp (case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end)  /*end*/
 /*cat*/ - timestamp (  case when wo.PMNUM is null then case  when w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour end else  case when case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  < '2016-04-01' then '2016-04-01' else case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end+ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour   end  end  )))/3600.0  /*end*/
 /*cat*/  else 0 end  work_time  /*end*/
 /*cat*/ ,/* norma норматив */ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end  norma  /*end*/
 /*cat*/ , /* просрочка  в нормативах alarm_time /norma */ case when  /*end*/
 /*cat*/ case when wo.PMNUM is null then case  when w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour end else  case when case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  < '2016-04-01' then '2016-04-01' else case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end+ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour   end  end   <  /*end*/
 /*cat*/ case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end   /*end*/
 /*cat*/ then   TIMESTAMPDIFF( 2,CHAR(timestamp (case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end)  /*end*/
 /*cat*/ - timestamp (  case when wo.PMNUM is null then case  when w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour end else  case when case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end + case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour  < '2016-04-01' then '2016-04-01' else case when cast(PMDUEDATE as timestamp)< w2.st then w2.st else cast(PMDUEDATE as timestamp) end+ case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end hour   end  end  )))/3600.0  /*end*/
 /*cat*/  else 0 end / case when case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end is null then 24 else  case when  PMDUEDATE is null  then DEADLINEWO_REALTIME else DEADLINEWO end end  /*end*/
 /*cat*/ ,/*isreg признак что НЗ был зарегистрирован в отчетном месяце для плановых по PMDUEDATE для внеплановых по дате APPR */  case when wo.PMNUM is null and w2.st between '2016-04-01' and '2016-05-01' then 1 	  when wo.PMNUM is not null and PMDUEDATE between '2016-04-01' and '2016-05-01' then 1 else 0 end   /*end*/
 /*cat*/  ,/*isclose признак что НЗ был pfrhsn в отчетном месяце по COMP */ case when w1.ed  is null  then 0  when w1.ed is not null and w1.ed between '2016-04-01' and '2016-05-01' then 1 else 0 end   /*end*/
 /*cat*/ from maximo.workorder wo inner join wost w2 on  wo.wonum=w2.wonum and w2.status='APPR' inner join maximo.COMMODITIES tCC on tCC.COMMODITY = WO.COMMODITY inner join maximo.LOCATIONs L_PM  on  L_PM.LOCATION =tCC.PODRM left  join wost w1 on  wo.wonum=w1.wonum and w1.status='COMP' left  join wost w3 on  wo.wonum=w3.wonum and w3.status='CONTR'  /*end*/
 /*cat*/ where  not (wo.status  in ( 'COMP','CLOSE') and  wo.STATUSDATE< '2016-04-01')and wo.el_wotype ='NZ'  and wo.status not in  ( 'CAN','CANPM','WSCH','INPLAN', 'WPLAN') And L_PM.SHORTPATH like '%МГВП%'   and PMDUEDATE < '2016-05-01'  /*end*/
 /*cat*/ and case when wo.status  in ('COMP','CONTR', 'CLOSE' ) then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end >    '2016-04-01' and (tCC.SERVICECAT ='SRV' or tCC.SERVICECAT is null )  /*end*/
 /*cat*/ and wo.PMNUM is not null /*end*/
 /*cat*/ union all  /*end*/
 /*cat*/ select  sr.TICKETID ,sr.status ,L_PM.SHORTPATH, sr.COMMODITYGROUP,sr.COMMODITY,sr.WORKGROUP, '' PRED, w2.st,  w3.ed  ,w1.ed, null PMDUEDATE  /*end*/
 /*cat*/ , case  when w2.st + 	case when  	DEADLINESR is null 	then 24 else  DEADLINESR end 	hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when  	DEADLINESR is null 	then 24 else  DEADLINESR end hour end  st_alarm  /*end*/
 /*cat*/ ,case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end   ed_alarm   /*end*/
 /*cat*/ ,case when  /*end*/
 /*cat*/ case  when w2.st + 	case when  	DEADLINESR is null 	then 24 else  DEADLINESR end 	hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when  	DEADLINESR is null 	then 24 else  DEADLINESR end hour end    <=  /*end*/
 /*cat*/ case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end  /*end*/
 /*cat*/ then   TIMESTAMPDIFF( 2,CHAR(timestamp (case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end)  /*end*/
 /*cat*/ - timestamp (   case  when w2.st + 	case when  	DEADLINESR is null 	then 24 else  DEADLINESR end 	hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when  	DEADLINESR is null 	then 24 else  DEADLINESR end end   )))/3600.0  /*end*/
 /*cat*/  else 0 end  work_time  /*end*/
 /*cat*/ ,/* norma норматив */ case when DEADLINESR is null then 24 else DEADLINESR end  norma  /*end*/
 /*cat*/ , /* просрочка  в нормативах alarm_time /norma */  case when  /*end*/
 /*cat*/ case  when w2.st + 	case when  	DEADLINESR is null 	then 24 else  DEADLINESR end 	hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when  	DEADLINESR is null 	then 24 else  DEADLINESR end hour end    <=  /*end*/
 /*cat*/ case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end  /*end*/
 /*cat*/ then   TIMESTAMPDIFF( 2,CHAR(timestamp (case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end)  /*end*/
 /*cat*/ - timestamp (   case  when w2.st + 	case when  	DEADLINESR is null 	then 24 else  DEADLINESR end 	hour < '2016-04-01' then  '2016-04-01' else  w2.st + case when  	DEADLINESR is null 	then 24 else  DEADLINESR end hour end   )))/3600.0  /*end*/
 /*cat*/  else 0 end / case when DEADLINESR is null then 24 else DEADLINESR end  /*end*/
 /*cat*/ ,/*isreg признак что НЗ был зарегистрирован в отчетном месяце для плановых по PMDUEDATE для внеплановых по дате APPR */  case when  w2.st between '2016-04-01' and '2016-05-01' then  1  else 0 end   /*end*/
 /*cat*/  ,/*isclose признак что НЗ был pfrhsn в отчетном месяце по COMP */ case when w1.ed  is null  then 0  when w1.ed is not null and w1.ed between '2016-04-01' and '2016-05-01' then 1 else 0 end   /*end*/
 /*cat*/ from maximo.ticket sr inner join srst w2 on  sr.TICKETID=w2.TICKETID and w2.status='ASSIGNED' inner join maximo.COMMODITIES tCC on tCC.COMMODITY = sr.COMMODITY  inner join maximo.LOCATIONs L_PM  on  L_PM.LOCATION =tCC.PODRM left  join srst w1 on  sr.TICKETID=w1.TICKETID and w1.status='CLOSED' left  join srst w3 on  sr.TICKETID=w3.TICKETID and w3.status='RESOLVED' where  not (sr.status  = 'CLOSED' and  sr.STATUSDATE< '2016-04-01')and sr.status not in  ( 'CANCELED','REVOKED','TOCHECK') And L_PM.SHORTPATH like '%МГВП%'     /*end*/
 /*cat*/ and case when sr.status = 'CLOSED' then   case  when w3.ed  is null or w3.ed> '2016-05-01'  then '2016-05-01' else  w3.ed end  else '2016-05-01' end    >    '2016-04-01' and (tCC.SERVICECAT ='SRV' or tCC.SERVICECAT is null ) ) /*end*/


 select 1 L1  ,L2,L3,L4,L5,L6,L7,L8,PT.RESPPARTYGROUP L9,PR.DISPLAYNAME L10 ,replace( COALESCE (VARCHAR_FORMAT (@FROM@,'DD.MM.YY HH24:MI:SS'),'') ,'00:00:00','') L11 ,replace( COALESCE (VARCHAR_FORMAT (@TO@,'DD.MM.YY HH24:MI:SS'),'') ,'00:00:00','') L12 
 from ( select   wo.PG , L_Pred.SHORTPATH L2,PG.DESCRIPTION L3,avg(wo.alarm_time) L4,avg(wo.alarm_norm) L5 , sum(is_close) L6,sum(is_reg) L7 ,case when sum(is_close) =0 and sum(is_reg) =0 then 0 else sum(is_close)/cast (case when sum(is_reg)=0 then sum (is_close) else sum(is_reg) end as FLOAT)  end L8 
 from WO inner join maximo.PERSONGROUP PG  on wo.PG=PG.PERSONGROUP inner join maximo.COMMODITIES CG  on CG.COMMODITY = WO.CG  inner join maximo.COMMODITIES Cc  on CC.COMMODITY = WO.CC  inner join maximo.LOCATIONS L_PODR on L_PODR.LOCATION = PG.PODR  inner join maximo.LOCATIONS L_Pred on L_Pred.LOCATION = L_PODR.PRED  
 where  L_PODR.status='OPERATING' and L_Pred.status='OPERATING' and L_PODR.typeunit=1 and L_Pred.ispred=1 
 group by L_Pred.SHORTPATH ,PG.DESCRIPTION  , wo.PG  
 having avg(wo.alarm_norm)>3 and case when sum(is_close) =0 and sum(is_reg) =0 then 0 else sum(is_close)/cast (case when sum(is_reg)=0 then sum (is_close) else sum(is_reg) end as FLOAT)  end   <0.7 
 ) f  inner join maximo.PERSONGROUPTEAM PT on f.pg=PT.PERSONGROUP  inner join maximo.PERSON PR on PR.PERSONID=PT.RESPPARTYGROUP where PT.MANAGER=1 and PR.STATUS ='ACTIVE' 

/*cat*/ Select wonum D1 , Wo.status D2, metod D3, CG.DESCRIPTION D4 ,CC.DESCRIPTION D5, PG.DESCRIPTION D6, L_Pred.SHORTPATH D7 /*end*/
/*cat*/ , /*D8*/  replace( COALESCE (VARCHAR_FORMAT (st_t,'DD.MM.YY HH24:MI:SS'),'') ,'00:00:00','')  D8 /*end*/
/*cat*/ , /*D9*/  replace(COALESCE (VARCHAR_FORMAT (ed_t,'DD.MM.YY HH24:MI:SS'),''),'00:00:00','')   D9 /*end*/
/*cat*/ , /*D10*/ replace(COALESCE (VARCHAR_FORMAT (t_comp,'DD.MM.YY HH24:MI:SS') ,''),'00:00:00','')  D10 /*end*/
/*cat*/ , /*D11*/ replace(COALESCE (VARCHAR_FORMAT (PMDUEDATE,'DD.MM.YY'),''),'00:00:00','')  D11 /*end*/
/*cat*/ , /*D12*/ replace(VARCHAR_FORMAT (st_alarm,'DD.MM.YY HH24:MI:SS'),'00:00:00','')  D12 /*end*/
/*cat*/ , /*D13*/ replace(VARCHAR_FORMAT (ed_alarm,'DD.MM.YY HH24:MI:SS'),'00:00:00','')  D13  /*end*/
/*cat*/ , alarm_time D14 , norma D15, alarm_norm D16  from WO /*end*/
/*cat*/ inner join maximo.PERSONGROUP PG  on wo.PG=PG.PERSONGROUP 
inner join maximo.COMMODITIES CG  on CG.COMMODITY = WO.CG  
inner join maximo.COMMODITIES Cc  on CC.COMMODITY = WO.CC 
inner join maximo.LOCATIONS L_PODR on L_PODR.LOCATION = PG.PODR 
inner join maximo.LOCATIONS L_Pred on L_Pred.LOCATION = L_PODR.PRED 
where  L_PODR.status='OPERATING' and L_Pred.status='OPERATING' and L_PODR.typeunit=1 and L_Pred.ispred=1    /*end*/