drop view ticket_resolution;
create view ticket_resolution as
select t1.id,t1.queue,t1.type,t1.owner,t1.subject,t1.initialpriority,t1.finalpriority,t1.priority,t1.timeestimated,t1.timeworked,t1.status,t1.sla,t1.timeleft,
t1.told,t1.starts::timestamp AT time zone 'utc' as starts,t1.started::timestamp AT time zone 'utc' as started,t1.due::timestamp AT time zone 'utc' due,t1.resolved::timestamp AT time zone 'utc' resolved,t1.lastupdatedby,t1.lastupdated::timestamp AT time zone 'utc' lastupdated,t1.creator,t1.created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM t1.created::timestamp AT time zone 'utc'))/60)::integer,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<31 and t1.sla='High Priority SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>60 and t1.sla='High Priority SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>30 and t1.sla='High Priority SR' and t1.status='in-progress'
and
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- (EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<46 and t1.sla='High Priority SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- (EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>45 and t1.sla='High Priority SR' and t1.status='in-progress'
and
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- (EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<61 and t1.sla='High Priority SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- (EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<91 and t1.sla='Normal SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>120 and t1.sla='Normal SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>90 and t1.sla='Normal SR' and t1.status='in-progress'
and
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<106 and t1.sla='Normal SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when (
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer>105 and t1.sla='Normal SR' and t1.status='in-progress'
and
floor(
(EXTRACT(EPOCH FROM current_timestamp)
- ( EXTRACT(EPOCH FROM t1.started::timestamp AT time zone 'utc')
+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))
)/60
)::integer<120 and t1.sla='Normal SR' and t1.status='in-progress'
) then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets as t1
left outer join track_ticket_move as t2 on t1.id=t2.ticket
where t1.status='in-progress' and t1.sla is not null group by t1.id order by id desc;
Comments
Post a Comment