ADDED TWO NEW CONDITION-
case when (EXTRACT(EPOCH FROM (created))/60>30 and sla='Normal SR' and status='new' then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created))/60>15 and sla='High Priority SR' and status='new' then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
# SO FINAL VIEW FOR TICKETS IS AS GIVEN BELOW-------====>>>
CREATE VIEW tickets_master AS
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>90 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH
from tickets;
New tickets_master
create view tickets_master as
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>60 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH
from tickets;
FINAL TICKETS_MASTER VIEW
create view tickets_master as
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>60 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH,
from tickets;
# END CREATION OF TICKETS VIEW_
CREATE TABLE darwindata (
id int(11) not null auto_increment,
employee_id int(11) not null,
first_name varchar(70) not null,
last_name varchar(20) not null,
sap_id int(11) not null,
company_email_id varchar(60),
designation varchar(60),
department varchar(60),
departments_hierarchy varchar(50),
office_area varchar(80),
office_city varchar(30),
office_state varchar(30),
office_country varchar(30),
date_of_joining varchar(50),
direct_manager_employee_id int(11),
hod varchar(30),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=203 DEFAULT CHARSET=latin1 ;
#3
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
IF str IS NULL
THEN
RETURN "";
END IF;
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c BETWEEN '0' AND '9' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
SELECT digits('$10.00Fr');
select digits(hod),hod as org from darwindata limit 1;
#returns 1000
## To find the datetime diffrence in minutes in postgresql
floor(EXTRACT(EPOCH FROM main.created-transaction_timestamp())/60)::integer
delimiter|
CREATE OR REPLACE FUNCTION ticket_status(i NUMERIC)
RETURN TABLE(ticketcurrsla TEXT) AS $$
DECLARE
rec RECORD;
BEGIN
EXECUTE 'select case when SLA_BREACH_ITDESK = 1 then Breach when SLA_ITDESK_REM = 1 then Reminder end as ticketcurrsla
from tickets_master where id = 1023';
RETURN ticketcurrsla;
END; $$ LANGUAGE plpgsql;
|
DELIMITER ;
CREATE VIEW darwindata_master AS
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id
View updated and new is-
create view darwindata_master as
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email,lvl2.employee_id
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id;
select case when (empjob='General Manager 3' || empjob='General Manager 2' || empjob='General Manager 1') then employee when (lvljobid='General Manager 3' || lvljobid='General Manager 2' || lvljobid='General Manager 1') then lvl1email when (lvl2jobid='General Manager 3' || lvl2jobid='General Manager 2' || lvl2jobid='General Manager 1') then lvl2email when (empjob='Assistant Vice President' ) then employee when (lvljobid='Assistant Vice President' ) then lvl1email when (lvl2jobid='Assistant Vice President') then lvl2email when (empjob='Vice President' ) then employee when (lvljobid='Vice President') then lvl1email when (lvl2jobid='Vice President') then lvl2email end as gmemailid from darwindata_master where employee='devendra.kumarsingh@timesinternet.in';
CREATE TABLE darwinhoddata (
id int(11) not null auto_increment,
company_email_id varchar(60),
designation varchar(60),
department varchar(60),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into darwinhoddata(job_level) select distinct job_level from darwindata where job_level in('Assistant Vice President','General Manager 2','General Manager 1','General Manager 3','Vice President');
create table department_new select * from department;
create table userldapdata_new select * from userldapdata;
alter table userldapdata_new add hodtitle varchar(40);
alter table userldapdata_new add usermob int(12);
CREATE UNIQUE INDEX useremail ON userldapdata_new(useremail);
CREATE UNIQUE INDEX userid ON userldapdata_new(userid);
alter table userldapdata_new drop usermob;
alter table userldapdata_new add usermob varchar(20);
create table newslaforasset(
id int(11) not null auto_increment,
user_office_loc varchar(40) not null,
asset_loc varchar(40) not null,
sla varchar(20) not null,
PRIMARY KEY (id)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ;
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ashram Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Udyog Vihar 3 Gurgaon','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Indiranagar','Indira Nager Bangalore','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Indiranagar','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Kormangala','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Primrose Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Raheja Plaza','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Mg Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Dhole Patil','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Koregaon Park','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Lower Parel','Kamala Mills Mumbai','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Lower Parel','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Mumbai','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Nandanam','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Suprageet Complex','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Banjara Hills','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Jubliee Hills','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Sector-16 Noida','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Gomti Nagar','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ajc Bose Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Banerjee Rd','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Camac Street','Any other','5 days');
-----------------------------
============================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE tobynew.assetissuelist SELECT * FROM live_sysadmin.assetissuelist;
-------------------------------------------------------------------------------
CREATE TABLE tobynew.costcenter SELECT * FROM live_sysadmin.costcenter;
CREATE TABLE tobynew.assettypelist SELECT * FROM live_sysadmin.assettypelist;
CREATE TABLE tobynew.defaultassets SELECT * FROM live_sysadmin.defaultassets;
CREATE TABLE tobynew.department SELECT * FROM live_sysadmin.department;
CREATE TABLE tobynew.icondetails SELECT * FROM live_sysadmin.icondetails;
CREATE TABLE tobynew.icontype SELECT * FROM live_sysadmin.icontype;
CREATE TABLE tobynew.icontypenew SELECT * FROM live_sysadmin.icontypenew;
CREATE TABLE tobynew.itdeskdetails SELECT * FROM live_sysadmin.itdeskdetails;
CREATE TABLE tobynew.othericontype SELECT * FROM live_sysadmin.othericontype;
CREATE TABLE tobynew.projectmapping SELECT * FROM live_sysadmin.projectmapping;
CREATE TABLE tobynew.sla SELECT * FROM live_sysadmin.sla;
CREATE TABLE tobynew.ticketdetails SELECT * FROM live_sysadmin.ticketdetails;
CREATE TABLE tobynew.ticketpriority SELECT * FROM live_sysadmin.ticketpriority;
CREATE TABLE tobynew.token SELECT * FROM live_sysadmin.token;
CREATE TABLE tobynew.userldapdata SELECT * FROM live_sysadmin.userldapdata;
CREATE TABLE tobynew.vertical SELECT * FROM live_sysadmin.vertical;
CREATE TABLE tobynew.darwindata SELECT * FROM live_sysadmin.darwindata;
CREATE TABLE tobynew.digits SELECT * FROM live_sysadmin.digits;
CREATE TABLE tobynew.darwindata_master SELECT * FROM live_sysadmin.darwindata_master;
CREATE TABLE tobynew.darwinhoddata SELECT * FROM live_sysadmin.darwinhoddata;
CREATE TABLE tobynew.department_new SELECT * FROM live_sysadmin.department_new;
CREATE TABLE tobynew.newslaforasset SELECT * FROM live_sysadmin.newslaforasset;
CREATE TABLE tobynew.darwindata SELECT * FROM live_sysadmin.darwindata;
CREATE TABLE tobynew.digits SELECT * FROM live_sysadmin.digits;
CREATE TABLE tobynew.darwindata_master SELECT * FROM live_sysadmin.darwindata_master;
CREATE TABLE tobynew.darwinhoddata SELECT * FROM live_sysadmin.darwinhoddata;
CREATE TABLE tobynew.department_new SELECT * FROM live_sysadmin.department_new;
CREATE TABLE tobynew.newslaforasset SELECT * FROM live_sysadmin.newslaforasset;
CREATE TABLE toby.gettopasseticonlist SELECT * FROM live_sysadmin.gettopasseticonlist;
CREATE TABLE tobynew.gettopasseticonlist SELECT * FROM live_sysadmin.gettopasseticonlist;
CREATE TABLE toby.rtassettypedetails2 SELECT * FROM live_sysadmin.rtassettypedetails2;
CREATE TABLE tobynew.rtassettypedetails2 SELECT * FROM live_sysadmin.rtassettypedetails2;
++++++++++++++++++++++++++++++++++++++++++++++
===============================================================
---------------------------------------------------------------------
1) change in db-=>
alter table assettypelist add name varchar(20);
update assettypelist set name='others' where id=14;
update assettypelist set name='desktop' where id=22;
update assettypelist set name='laptop' where id=24;
update assettypelist set name='Data Card' where id=26;
update assettypelist set name='mouse' where id=29;
update assettypelist set name='keyboard' where id=10;
update assettypelist set name='bag' where id=37;
CREATE TABLE c_users_ldapdata (
id int(11) not null auto_increment,
title varchar(60) not null,
employeeid varchar(30) not null,
department varchar(60) not null,
emailid varchar(50) not null,
name varchar(35) not null,
location varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE tobynew.c_users_ldapdata SELECT * FROM toby.c_users_ldapdata;
select dept.department,dept.deptheademail,c.custlocation
from department as dept
join c_users_ldapdata as c on dept.department=c.department
where c.emailid='dipti.tandon@timesinternet.in';
select * from c_users_ldapdata where emailid='dipti.tandon@timesinternet.in';
select * from department where department='TIL - TECHNOLOGY';
CREATE TABLE hoddelegate (
id int(11) not null auto_increment,
hod_email_id varchar(60),
delegate_email varchar(60),
department varchar(60),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
alter table hoddelegate add status varchar(10);
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM (started-created)))/60<=30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=30 and (EXTRACT(EPOCH FROM (started-created)))/60<45 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=45 and (EXTRACT(EPOCH FROM (started-created)))/60<60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((EXTRACT(EPOCH FROM (started-created)))/60<=120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=90 and (EXTRACT(EPOCH FROM (started-created)))/60<105 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=105 and (EXTRACT(EPOCH FROM (started-created)))/60<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
tct_mastr_new ======+++++ VIEW RE-CREATED AS GIVEN BELOW==>>>>>
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<=30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=30 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<45 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=45 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<=120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=90 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<105 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=105 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
//**** TCT_MASTR_NEW VIEW MODIFIED AND CREATED AGAIN ******//
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<6 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>14 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>9 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<16 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>4 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<11 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<11 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>29 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>19 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<26 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>24 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<31 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM created))/60>59 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>29 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>44 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<121 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM created))/60>119 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>89 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>104 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<121 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
/* ticket_resolution VIEW */
create view ticket_resolution as
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
/* END VIEW ticket_RESOLUTION */
/* ticket_resolution VIEW NEW with having hold time of ticket */
create view ticket_resolution as
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
====>>> UPDATED ONE IS BELOW ==++==-->
====>>> UPDATED ONE IS BELOW ==++==-->
====>>> UPDATED ONE IS BELOW ==++==--> ====>>> UPDATED ONE IS BELOW ==++==-->
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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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;
=====>>>>>>> ++++++++++++++++===>
====-->>
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
----==>>
==>>>final VIEW ticket_RESOLUTION new with having ticket hold time
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))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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;
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))/60)::integer,
case when (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>30
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>45
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>90
and
(floor
(EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>105
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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;
FINAL ONE FINAL ONE FINAL ONE ========== }}}} {{{{{{{{{
????????????????????$$$$$$$$$$$$$$$$$###########((((((((({{}]]]]]]]]]]]]]]]
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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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;
/* END VIEW ticket_RESOLUTION new with having ticket hold time */
/* VIEW ticket_response */
CREATE VIEW ticket_response AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>=0 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<6 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>10 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<16 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>5 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<11 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>=0 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<16 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>20 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<26 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>25 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<31 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE
from tickets where status='new' and sla is not null order by id desc;
/* END VIEW ticket_response */
/*new ticket_response ticket_response ticket_response*/
CREATE VIEW ticket_response AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<21 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE
from tickets where status='new' and sla is not null order by id desc;
// Response breach in 5 minutes
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0')
AND ( CachedGroupMembers_2.GroupId = Groups_1.id )
JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id ) WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new' and main.sla='Normal SR'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>25
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<31
order by main.id desc limit '10' offset '0'
// Response breach in 10 minutes
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>20
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<26
and main.sla='Normal SR'
order by main.id desc limit '10' offset '0';
// Response 15 minutes OK status
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') and
main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>=0
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<16
and main.sla='Normal SR' order by main.id desc limit '10' offset '0'
// Response breached
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>30
and main.sla='Normal SR' order by main.id desc limit '10' offset '0'
select id,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new')
then 1 else 0 end as SLA_NORML_ITDSK_BREACH
from tickets order by id desc;
Below given sql is for Quiz database
CREATE TABLE category (
id int(11) not null auto_increment,
name varchar(100) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE question (
questionid int(11) not null auto_increment,
name varchar(500),
catid int(11) not null,
PRIMARY KEY (questionid),
FOREIGN KEY(catid) REFERENCES category(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE answer (
answerid int(11) not null auto_increment,
name varchar(500) not null,
questionid int(11) not null,
status enum('false','true') default 'false',
PRIMARY KEY (answerid),
FOREIGN KEY(questionid) REFERENCES question(questionid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE quizeattendent (
id int(11) not null auto_increment,
userid varchar(100) not null,
catid int(10),
questionid int(10),
answerid int(10),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into category(name) values('Access control mechanisms');
insert into category(name) values('Information value');
insert into category(name) values('Malicious software and basic safe computing');
insert into category(name) values('Password management');
insert into category(name) values('Physical security mechanisms');
insert into category(name) values('Safeguarding data');
insert into question(name,catid) values('Your supervisor is very busy and asks you to log into Timescape using her user-ID and password to apply leave for their attendance. What should you do?',1);
insert into question(name,catid) values('Number of stated in India?',1);
insert into question(name,catid) values('What are the Information Classification Labels for all types of Information in TIL?',2);
insert into question(name,catid) values('Chief Minister of Delhi?',2);
insert into question(name,catid) values('You noticed some unsual activity on your computer. It seems like your computer has been compromised. What would you do first?',3);
insert into question(name,catid) values('Captian of India cricket team?',3);
insert into question(name,catid) values('What is one of the ways that you can secure your password from disclosure?)',4);
insert into question(name,catid) values('President of Congress?',4);
insert into question(name,catid) values('It is OK to leave confidential information on a desk in the office building?',5);
insert into question(name,catid) values('Prime Minister of India?',5);
insert into question(name,catid) values('Where can you maintain your data backups in TIL?',6);
insert into question(name,catid) values('Number of hours in a day?',6);
insert into answer (name,questionid,status) values('It’s your boss, so it’s okay to do this.',1,true);
insert into answer (name,questionid) values('Ignore the request and hope they forget.',1);
insert into answer (name,questionid) values('Decline the request and remind your supervisor that it is against company policy.',1);
insert into answer (name,questionid) values('Apply for their leave, as well fill the conveyance, communications claims etc. also',1);
insert into answer (name,questionid,status) values('29',2);
insert into answer (name,questionid,status) values('28',2,'true');
insert into answer (name,questionid) values('27',2);
insert into answer (name,questionid) values('26',2);
insert into answer (name,questionid) values("Personal, Familys, Neighbours, Company's",3);
insert into answer (name,questionid,status) values("Facebook Posts, Tweets, Instagram Stories, LinkedIn Profile",3,'true');
insert into answer (name,questionid) values('Public, Proprietary, Client Confidential, TIL Confidential',3);
insert into answer (name,questionid) values('Information Classification? Huh?',3);
insert into answer (name,questionid) values('Suresh',4);
insert into answer (name,questionid) values('Mukesh',4);
insert into answer (name,questionid) values('Anand',4);
insert into answer (name,questionid,status) values('Arvind',4,'true');
insert into answer (name,questionid) values("Contact IT Desk",5);
insert into answer (name,questionid) values("Disconnect your machine from network",5);
insert into answer (name,questionid) values("Disconnect your machine from network",5);
insert into answer (name,questionid) values("Email IT Security team at nsoc@timesinternet.in",5);
insert into answer (name,questionid) values("Kohli",6);
insert into answer (name,questionid,status) values("Dhoni",6,'true');
insert into answer (name,questionid) values("Dhawan",6);
insert into answer (name,questionid) values("Hardik",6);
insert into answer (name,questionid) values("Memorize it",7);
insert into answer (name,questionid,status) values("Write in on a post-it and stick on your computer",7,'true');
insert into answer (name,questionid) values("Its anyways Times123, and everybody else uses that, so why bother",7);
insert into answer (name,questionid) values("Write it on a piece of paper and keep it secure on your person at all times",7);
insert into answer (name,questionid) values("Yes. The office is in a secure area so there is no problem.",9);
insert into answer (name,questionid) values("Yes. I have been assigned a cabin and nobody comes inside my cabin unless i am there",9);
insert into answer (name,questionid,status) values("No. Confidential information should never be left where any unauthorized personnel can view it.",9,'true');
insert into answer (name,questionid) values("None of the above.",9);
insert into answer (name,questionid) values("My data is not important and dont need any backups",11);
insert into answer (name,questionid) values("Facebook already has all my data",11);
insert into answer (name,questionid) values("Pen Drive that I got from home",11);
insert into answer (name,questionid,status) values("My Drive & Team Drive on GSuite",11,'true');
CREATE TABLE active_engineer (
id int(11) not null auto_increment,
name varchar(500) not null,
status enum('active','inactive') default 'inactive',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into active_engineer(name,status) values('c-Prabhat.Tiwari','active');
insert into active_engineer(name,status) values('c-Jitender.Yadav','active');
insert into active_engineer(name,status) values('c-Mukesh.Prasad','active');
insert into active_engineer(name,status) values('c-Rati.Gola','active');
insert into active_engineer(name,status) values('c-Sapna.Gola','active');
insert into active_engineer(name,status) values('c-nikhil.negi','active');
insert into active_engineer(name,status) values('c-kameshwar.v','active');
insert into active_engineer(name,status) values('c-pranshu.ohri','active');
insert into active_engineer(name,status) values('c-Rahul.Soni','active');
insert into active_engineer(name,status) values('c-Kuldeep.Mehta','active');
insert into active_engineer(name,status) values('c-Rajesh.Chauhan','active');
insert into active_engineer(name,status) values('c-shyam.nishad','active');
insert into active_engineer(name,status) values('c-Vineet.Tomar','active');
insert into active_engineer(name,status) values('c-Mohit.Chandila','active');
insert into active_engineer(name,status) values('c-Devendra.Bhatt','active');
insert into active_engineer(name,status) values('c-Jay.P','active');
insert into active_engineer(name,status) values('c-Himanshu.Singh','active');
insert into active_engineer(name,status) values('c-Durgesh.Singh','active');
insert into active_engineer(name,status) values('c-Naveen.Rana','active');
insert into active_engineer(name,status) values('chanakya.neeraj','active');
insert into active_engineer(name,status) values('neeraj.kumar2','active');
insert into active_engineer(name,status) values('Salman.Ahmed','active');
insert into active_engineer(name,status) values('Vijay.Negi','active');
insert into active_engineer(name,status) values('subodh.mamgain','active');
insert into active_engineer(name,status) values('c-Aviral.Gupta','active');
insert into active_engineer(name,status) values('Parmanand.Tiwari','active');
insert into active_engineer(name,status) values('c-Shoeb.Khan','active');
insert into active_engineer(name,status) values('Pradeep.Kumar1','active');
insert into active_engineer(name,status) values('Panditaray.Madditot','active');
insert into active_engineer(name,status) values('c-Ravi.Pandey','active');
insert into active_engineer(name,status) values('c-Gaurav.khurana','active');
create database keystonesrvack;
CREATE TABLE keystonesrvack.keystoneserverack SELECT * FROM live_sysadmin.ticketdetails;
mysql -udb_user -p'S3curity@@' -h 172.29.102.103
CREATE TABLE org_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE helpdesk_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE gsuite_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE track_ticket_move (
id int(11) not null auto_increment,
created_time int(10),
started_time int(10),
hold_start_time int(10),
hold_end_time int(10),
hold_count int(5),
current_status varchar(15),
ticket int(10),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
alter table track_ticket_move modify current_status varchar(25);
==>
RT database, database postgresql->
CREATE TABLE track_ticket_move (
id integer primary key ,
created_time integer,
started_time integer,
hold_start_time integer,
hold_end_time integer,
hold_count integer,
current_status varchar(25),
ticket integer
);
CREATE INDEX ticket_index on track_ticket_move (ticket);
----------------------------------------
grant select on all tables in schema public to "svc.rt16";
grant select on all tables in schema public to devendrak;
grant all privileges on all tables in schema public to rtappusr;
==<
=============== ============== ===>>>>>>>>>>>
==========
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 , (EXTRACT(EPOCH FROM t1.started)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0) - EXTRACT(EPOCH FROM created)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0))/60::integer, case when ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60 )::integer>30 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0) )- (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>45 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>90 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>105 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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;
drop 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 ,
(EXTRACT(EPOCH FROM t1.started)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0) - EXTRACT(EPOCH FROM created)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0))/60::integer,
case when ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)))/60 )::integer>30 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0) )- (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>45 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>90 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>105 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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;
=============>
DARWIN DATA TABLE
=================>
CREATE TABLE darwindata_new (
id int(11) NOT NULL DEFAULT '0',
employee_id int(11) NOT NULL,
first_name varchar(70) NOT NULL,
last_name varchar(20) NOT NULL,
sap_id int(11) NOT NULL,
company_email_id varchar(60) DEFAULT NULL,
designation varchar(60) DEFAULT NULL,
department varchar(60) DEFAULT NULL,
departments_hierarchy varchar(50) DEFAULT NULL,
office_area varchar(80) DEFAULT NULL,
office_city varchar(30) DEFAULT NULL,
office_state varchar(30) DEFAULT NULL,
office_country varchar(30) DEFAULT NULL,
date_of_joining varchar(50) DEFAULT NULL,
direct_manager_employee_id int(11) DEFAULT NULL,
hod varchar(30) DEFAULT NULL,
job_level varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
<===================
<==================
create view darwindata_master_new as
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email,lvl2.employee_id
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id;
<================
<===============
case when (EXTRACT(EPOCH FROM (created))/60>30 and sla='Normal SR' and status='new' then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created))/60>15 and sla='High Priority SR' and status='new' then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
# SO FINAL VIEW FOR TICKETS IS AS GIVEN BELOW-------====>>>
CREATE VIEW tickets_master AS
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when (EXTRACT(EPOCH FROM (created::timestamp AT time zone 'utc'))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>90 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when (EXTRACT(EPOCH FROM (started::timestamp AT time zone 'utc'-created::timestamp AT time zone 'utc'))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH
from tickets;
New tickets_master
create view tickets_master as
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>60 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH
from tickets;
FINAL TICKETS_MASTER VIEW
create view tickets_master as
select id,effectiveid,ismerged,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_REMINDER,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_REMNDR,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>60 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_REMINDR,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGR_BREACH,
from tickets;
# END CREATION OF TICKETS VIEW_
CREATE TABLE darwindata (
id int(11) not null auto_increment,
employee_id int(11) not null,
first_name varchar(70) not null,
last_name varchar(20) not null,
sap_id int(11) not null,
company_email_id varchar(60),
designation varchar(60),
department varchar(60),
departments_hierarchy varchar(50),
office_area varchar(80),
office_city varchar(30),
office_state varchar(30),
office_country varchar(30),
date_of_joining varchar(50),
direct_manager_employee_id int(11),
hod varchar(30),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=203 DEFAULT CHARSET=latin1 ;
#3
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
IF str IS NULL
THEN
RETURN "";
END IF;
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c BETWEEN '0' AND '9' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
SELECT digits('$10.00Fr');
select digits(hod),hod as org from darwindata limit 1;
#returns 1000
## To find the datetime diffrence in minutes in postgresql
floor(EXTRACT(EPOCH FROM main.created-transaction_timestamp())/60)::integer
delimiter|
CREATE OR REPLACE FUNCTION ticket_status(i NUMERIC)
RETURN TABLE(ticketcurrsla TEXT) AS $$
DECLARE
rec RECORD;
BEGIN
EXECUTE 'select case when SLA_BREACH_ITDESK = 1 then Breach when SLA_ITDESK_REM = 1 then Reminder end as ticketcurrsla
from tickets_master where id = 1023';
RETURN ticketcurrsla;
END; $$ LANGUAGE plpgsql;
|
DELIMITER ;
CREATE VIEW darwindata_master AS
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id
View updated and new is-
create view darwindata_master as
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email,lvl2.employee_id
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id;
select case when (empjob='General Manager 3' || empjob='General Manager 2' || empjob='General Manager 1') then employee when (lvljobid='General Manager 3' || lvljobid='General Manager 2' || lvljobid='General Manager 1') then lvl1email when (lvl2jobid='General Manager 3' || lvl2jobid='General Manager 2' || lvl2jobid='General Manager 1') then lvl2email when (empjob='Assistant Vice President' ) then employee when (lvljobid='Assistant Vice President' ) then lvl1email when (lvl2jobid='Assistant Vice President') then lvl2email when (empjob='Vice President' ) then employee when (lvljobid='Vice President') then lvl1email when (lvl2jobid='Vice President') then lvl2email end as gmemailid from darwindata_master where employee='devendra.kumarsingh@timesinternet.in';
CREATE TABLE darwinhoddata (
id int(11) not null auto_increment,
company_email_id varchar(60),
designation varchar(60),
department varchar(60),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into darwinhoddata(job_level) select distinct job_level from darwindata where job_level in('Assistant Vice President','General Manager 2','General Manager 1','General Manager 3','Vice President');
create table department_new select * from department;
create table userldapdata_new select * from userldapdata;
alter table userldapdata_new add hodtitle varchar(40);
alter table userldapdata_new add usermob int(12);
CREATE UNIQUE INDEX useremail ON userldapdata_new(useremail);
CREATE UNIQUE INDEX userid ON userldapdata_new(userid);
alter table userldapdata_new drop usermob;
alter table userldapdata_new add usermob varchar(20);
create table newslaforasset(
id int(11) not null auto_increment,
user_office_loc varchar(40) not null,
asset_loc varchar(40) not null,
sla varchar(20) not null,
PRIMARY KEY (id)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ;
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Daryaganj','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ashram Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Udyog Vihar 3 Gurgaon','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ecstasy It Park','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Indiranagar','Indira Nager Bangalore','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Indiranagar','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Kormangala','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Primrose Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Raheja Plaza','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Mg Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Dhole Patil','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Koregaon Park','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Lower Parel','Kamala Mills Mumbai','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Lower Parel','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Mumbai','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Nandanam','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Suprageet Complex','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Banjara Hills','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Jubliee Hills','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Sector-16 Noida','2 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Film City','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Gomti Nagar','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Sector-16 Noida','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Udyog Vihar 3 Gurgaon','24 Hours');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Prius Heights','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Ajc Bose Road','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Banerjee Rd','Any other','5 days');
insert into newslaforasset(user_office_loc,asset_loc,sla) values('Camac Street','Any other','5 days');
-----------------------------
============================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE tobynew.assetissuelist SELECT * FROM live_sysadmin.assetissuelist;
-------------------------------------------------------------------------------
CREATE TABLE tobynew.costcenter SELECT * FROM live_sysadmin.costcenter;
CREATE TABLE tobynew.assettypelist SELECT * FROM live_sysadmin.assettypelist;
CREATE TABLE tobynew.defaultassets SELECT * FROM live_sysadmin.defaultassets;
CREATE TABLE tobynew.department SELECT * FROM live_sysadmin.department;
CREATE TABLE tobynew.icondetails SELECT * FROM live_sysadmin.icondetails;
CREATE TABLE tobynew.icontype SELECT * FROM live_sysadmin.icontype;
CREATE TABLE tobynew.icontypenew SELECT * FROM live_sysadmin.icontypenew;
CREATE TABLE tobynew.itdeskdetails SELECT * FROM live_sysadmin.itdeskdetails;
CREATE TABLE tobynew.othericontype SELECT * FROM live_sysadmin.othericontype;
CREATE TABLE tobynew.projectmapping SELECT * FROM live_sysadmin.projectmapping;
CREATE TABLE tobynew.sla SELECT * FROM live_sysadmin.sla;
CREATE TABLE tobynew.ticketdetails SELECT * FROM live_sysadmin.ticketdetails;
CREATE TABLE tobynew.ticketpriority SELECT * FROM live_sysadmin.ticketpriority;
CREATE TABLE tobynew.token SELECT * FROM live_sysadmin.token;
CREATE TABLE tobynew.userldapdata SELECT * FROM live_sysadmin.userldapdata;
CREATE TABLE tobynew.vertical SELECT * FROM live_sysadmin.vertical;
CREATE TABLE tobynew.darwindata SELECT * FROM live_sysadmin.darwindata;
CREATE TABLE tobynew.digits SELECT * FROM live_sysadmin.digits;
CREATE TABLE tobynew.darwindata_master SELECT * FROM live_sysadmin.darwindata_master;
CREATE TABLE tobynew.darwinhoddata SELECT * FROM live_sysadmin.darwinhoddata;
CREATE TABLE tobynew.department_new SELECT * FROM live_sysadmin.department_new;
CREATE TABLE tobynew.newslaforasset SELECT * FROM live_sysadmin.newslaforasset;
CREATE TABLE tobynew.darwindata SELECT * FROM live_sysadmin.darwindata;
CREATE TABLE tobynew.digits SELECT * FROM live_sysadmin.digits;
CREATE TABLE tobynew.darwindata_master SELECT * FROM live_sysadmin.darwindata_master;
CREATE TABLE tobynew.darwinhoddata SELECT * FROM live_sysadmin.darwinhoddata;
CREATE TABLE tobynew.department_new SELECT * FROM live_sysadmin.department_new;
CREATE TABLE tobynew.newslaforasset SELECT * FROM live_sysadmin.newslaforasset;
CREATE TABLE toby.gettopasseticonlist SELECT * FROM live_sysadmin.gettopasseticonlist;
CREATE TABLE tobynew.gettopasseticonlist SELECT * FROM live_sysadmin.gettopasseticonlist;
CREATE TABLE toby.rtassettypedetails2 SELECT * FROM live_sysadmin.rtassettypedetails2;
CREATE TABLE tobynew.rtassettypedetails2 SELECT * FROM live_sysadmin.rtassettypedetails2;
++++++++++++++++++++++++++++++++++++++++++++++
===============================================================
---------------------------------------------------------------------
1) change in db-=>
alter table assettypelist add name varchar(20);
update assettypelist set name='others' where id=14;
update assettypelist set name='desktop' where id=22;
update assettypelist set name='laptop' where id=24;
update assettypelist set name='Data Card' where id=26;
update assettypelist set name='mouse' where id=29;
update assettypelist set name='keyboard' where id=10;
update assettypelist set name='bag' where id=37;
CREATE TABLE c_users_ldapdata (
id int(11) not null auto_increment,
title varchar(60) not null,
employeeid varchar(30) not null,
department varchar(60) not null,
emailid varchar(50) not null,
name varchar(35) not null,
location varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE tobynew.c_users_ldapdata SELECT * FROM toby.c_users_ldapdata;
select dept.department,dept.deptheademail,c.custlocation
from department as dept
join c_users_ldapdata as c on dept.department=c.department
where c.emailid='dipti.tandon@timesinternet.in';
select * from c_users_ldapdata where emailid='dipti.tandon@timesinternet.in';
select * from department where department='TIL - TECHNOLOGY';
CREATE TABLE hoddelegate (
id int(11) not null auto_increment,
hod_email_id varchar(60),
delegate_email varchar(60),
department varchar(60),
job_level varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
alter table hoddelegate add status varchar(10);
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM (started-created)))/60<=30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=30 and (EXTRACT(EPOCH FROM (started-created)))/60<45 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=45 and (EXTRACT(EPOCH FROM (started-created)))/60<60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((EXTRACT(EPOCH FROM (started-created)))/60<=120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=90 and (EXTRACT(EPOCH FROM (started-created)))/60<105 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM (started-created)))/60>=105 and (EXTRACT(EPOCH FROM (started-created)))/60<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
tct_mastr_new ======+++++ VIEW RE-CREATED AS GIVEN BELOW==>>>>>
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=1 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<10 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<=30 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=30 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<45 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=45 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<=120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=90 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<105 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>=105 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
//**** TCT_MASTR_NEW VIEW MODIFIED AND CREATED AGAIN ******//
CREATE VIEW tct_mastr_new AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<6 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>14 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>9 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<16 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>4 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<11 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<11 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>29 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>19 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<26 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>24 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<31 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM created))/60>59 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>29 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>44 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<121 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((EXTRACT(EPOCH FROM created))/60>119 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>89 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60>104 and (EXTRACT(EPOCH FROM started) - EXTRACT(EPOCH FROM created))/60<121 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets order by id desc;
/* ticket_resolution VIEW */
create view ticket_resolution as
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
/* END VIEW ticket_RESOLUTION */
/* ticket_resolution VIEW NEW with having hold time of ticket */
create view ticket_resolution as
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
====>>> UPDATED ONE IS BELOW ==++==-->
====>>> UPDATED ONE IS BELOW ==++==-->
====>>> UPDATED ONE IS BELOW ==++==--> ====>>> UPDATED ONE IS BELOW ==++==-->
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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp)+sum(t2.hold_end_time-t2.hold_start_time) - EXTRACT(EPOCH FROM t1.created)+sum(t2.hold_end_time-t2.hold_start_time))/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;
=====>>>>>>> ++++++++++++++++===>
====-->>
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
floor((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<31 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>60 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<46 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<61 and sla='High Priority SR' and status='in-progress') then 1 else 0 end as SLA_HIGH_ENGGR_FIFTEEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<91 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<106 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_THIRTY,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM started))/60)::integer<120 and sla='Normal SR' and status='in-progress') then 1 else 0 end as SLA_NORML_ENGGR_FIFTEEN
from tickets where status='in-progress' and sla is not null order by id desc;
----==>>
==>>>final VIEW ticket_RESOLUTION new with having ticket hold time
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))/60)::integer,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>30 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>45 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>90 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60)::integer>105 and (floor(EXTRACT(EPOCH FROM current_timestamp) - (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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;
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))/60)::integer,
case when (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>30
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>45
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>90
and
(floor
(EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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 (
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0))/60
)::integer>105
and
(
EXTRACT(EPOCH FROM current_timestamp) -
(EXTRACT(EPOCH FROM t1.started)+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;
FINAL ONE FINAL ONE FINAL ONE ========== }}}} {{{{{{{{{
????????????????????$$$$$$$$$$$$$$$$$###########((((((((({{}]]]]]]]]]]]]]]]
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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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.created::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;
/* END VIEW ticket_RESOLUTION new with having ticket hold time */
/* VIEW ticket_response */
CREATE VIEW ticket_response AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>=0 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<6 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>10 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<16 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>5 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<11 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>=0 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<16 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>30 and sla='Normal SR' and status='new') then 1
else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>20 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<26 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer>25 AND (floor(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60)::integer<31 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE
from tickets where status='new' and sla is not null order by id desc;
/* END VIEW ticket_response */
/*new ticket_response ticket_response ticket_response*/
CREATE VIEW ticket_response AS
select id,queue,type,owner,subject,initialpriority,finalpriority,priority,timeestimated,timeworked,status,sla,timeleft,
told,starts::timestamp AT time zone 'utc' as starts,started::timestamp AT time zone 'utc' as started,due::timestamp AT time zone 'utc' due,resolved::timestamp AT time zone 'utc' resolved,lastupdatedby,lastupdated::timestamp AT time zone 'utc' lastupdated,creator,created::timestamp AT time zone 'utc' as created ,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=5 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>15 and sla='High Priority SR' and status='new') then 1
else 0 end as SLA_HIGH_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>10 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=15 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_FIVE,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>5 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=10 and sla='High Priority SR' and status='new') then 1 else 0 end as SLA_HIGH_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>0 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<21 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_OK,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_BREACH,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>20 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=25 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_TEN,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>25 AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60<=30 and sla='Normal SR' and status='new') then 1 else 0 end as SLA_NORML_ITDSK_FIVE
from tickets where status='new' and sla is not null order by id desc;
// Response breach in 5 minutes
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0')
AND ( CachedGroupMembers_2.GroupId = Groups_1.id )
JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id ) WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new' and main.sla='Normal SR'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>25
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<31
order by main.id desc limit '10' offset '0'
// Response breach in 10 minutes
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>20
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<26
and main.sla='Normal SR'
order by main.id desc limit '10' offset '0';
// Response 15 minutes OK status
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') and
main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>=0
AND (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60<16
and main.sla='Normal SR' order by main.id desc limit '10' offset '0'
// Response breached
SELECT main.id,main.subject,users_3.name as ticketrequester,main.status,to_char(main.created::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as created,to_char(main.lastupdated::timestamp AT time zone 'utc', 'YYYY-MM-DD HH:MI:SS') as lastupdated,users_4.name,users_4.city,main.priority,count(*) over() FROM Tickets main JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND (Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.Disabled = '0') AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) JOIN queues queues_3 ON ( main.queue = queues_3.id) JOIN users users_4 ON ( main.owner = users_4.id )
WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket')
and main.created>='2018-09-16 09:55:23' and main.created<='2018-10-15 23:59:59' and main.status='new'
and (EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM main.created))/60>30
and main.sla='Normal SR' order by main.id desc limit '10' offset '0'
select id,
case when ((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM created))/60>=30 and sla='Normal SR' and status='new')
then 1 else 0 end as SLA_NORML_ITDSK_BREACH
from tickets order by id desc;
Below given sql is for Quiz database
CREATE TABLE category (
id int(11) not null auto_increment,
name varchar(100) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE question (
questionid int(11) not null auto_increment,
name varchar(500),
catid int(11) not null,
PRIMARY KEY (questionid),
FOREIGN KEY(catid) REFERENCES category(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE answer (
answerid int(11) not null auto_increment,
name varchar(500) not null,
questionid int(11) not null,
status enum('false','true') default 'false',
PRIMARY KEY (answerid),
FOREIGN KEY(questionid) REFERENCES question(questionid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE quizeattendent (
id int(11) not null auto_increment,
userid varchar(100) not null,
catid int(10),
questionid int(10),
answerid int(10),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into category(name) values('Access control mechanisms');
insert into category(name) values('Information value');
insert into category(name) values('Malicious software and basic safe computing');
insert into category(name) values('Password management');
insert into category(name) values('Physical security mechanisms');
insert into category(name) values('Safeguarding data');
insert into question(name,catid) values('Your supervisor is very busy and asks you to log into Timescape using her user-ID and password to apply leave for their attendance. What should you do?',1);
insert into question(name,catid) values('Number of stated in India?',1);
insert into question(name,catid) values('What are the Information Classification Labels for all types of Information in TIL?',2);
insert into question(name,catid) values('Chief Minister of Delhi?',2);
insert into question(name,catid) values('You noticed some unsual activity on your computer. It seems like your computer has been compromised. What would you do first?',3);
insert into question(name,catid) values('Captian of India cricket team?',3);
insert into question(name,catid) values('What is one of the ways that you can secure your password from disclosure?)',4);
insert into question(name,catid) values('President of Congress?',4);
insert into question(name,catid) values('It is OK to leave confidential information on a desk in the office building?',5);
insert into question(name,catid) values('Prime Minister of India?',5);
insert into question(name,catid) values('Where can you maintain your data backups in TIL?',6);
insert into question(name,catid) values('Number of hours in a day?',6);
insert into answer (name,questionid,status) values('It’s your boss, so it’s okay to do this.',1,true);
insert into answer (name,questionid) values('Ignore the request and hope they forget.',1);
insert into answer (name,questionid) values('Decline the request and remind your supervisor that it is against company policy.',1);
insert into answer (name,questionid) values('Apply for their leave, as well fill the conveyance, communications claims etc. also',1);
insert into answer (name,questionid,status) values('29',2);
insert into answer (name,questionid,status) values('28',2,'true');
insert into answer (name,questionid) values('27',2);
insert into answer (name,questionid) values('26',2);
insert into answer (name,questionid) values("Personal, Familys, Neighbours, Company's",3);
insert into answer (name,questionid,status) values("Facebook Posts, Tweets, Instagram Stories, LinkedIn Profile",3,'true');
insert into answer (name,questionid) values('Public, Proprietary, Client Confidential, TIL Confidential',3);
insert into answer (name,questionid) values('Information Classification? Huh?',3);
insert into answer (name,questionid) values('Suresh',4);
insert into answer (name,questionid) values('Mukesh',4);
insert into answer (name,questionid) values('Anand',4);
insert into answer (name,questionid,status) values('Arvind',4,'true');
insert into answer (name,questionid) values("Contact IT Desk",5);
insert into answer (name,questionid) values("Disconnect your machine from network",5);
insert into answer (name,questionid) values("Disconnect your machine from network",5);
insert into answer (name,questionid) values("Email IT Security team at nsoc@timesinternet.in",5);
insert into answer (name,questionid) values("Kohli",6);
insert into answer (name,questionid,status) values("Dhoni",6,'true');
insert into answer (name,questionid) values("Dhawan",6);
insert into answer (name,questionid) values("Hardik",6);
insert into answer (name,questionid) values("Memorize it",7);
insert into answer (name,questionid,status) values("Write in on a post-it and stick on your computer",7,'true');
insert into answer (name,questionid) values("Its anyways Times123, and everybody else uses that, so why bother",7);
insert into answer (name,questionid) values("Write it on a piece of paper and keep it secure on your person at all times",7);
insert into answer (name,questionid) values("Yes. The office is in a secure area so there is no problem.",9);
insert into answer (name,questionid) values("Yes. I have been assigned a cabin and nobody comes inside my cabin unless i am there",9);
insert into answer (name,questionid,status) values("No. Confidential information should never be left where any unauthorized personnel can view it.",9,'true');
insert into answer (name,questionid) values("None of the above.",9);
insert into answer (name,questionid) values("My data is not important and dont need any backups",11);
insert into answer (name,questionid) values("Facebook already has all my data",11);
insert into answer (name,questionid) values("Pen Drive that I got from home",11);
insert into answer (name,questionid,status) values("My Drive & Team Drive on GSuite",11,'true');
CREATE TABLE active_engineer (
id int(11) not null auto_increment,
name varchar(500) not null,
status enum('active','inactive') default 'inactive',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
insert into active_engineer(name,status) values('c-Prabhat.Tiwari','active');
insert into active_engineer(name,status) values('c-Jitender.Yadav','active');
insert into active_engineer(name,status) values('c-Mukesh.Prasad','active');
insert into active_engineer(name,status) values('c-Rati.Gola','active');
insert into active_engineer(name,status) values('c-Sapna.Gola','active');
insert into active_engineer(name,status) values('c-nikhil.negi','active');
insert into active_engineer(name,status) values('c-kameshwar.v','active');
insert into active_engineer(name,status) values('c-pranshu.ohri','active');
insert into active_engineer(name,status) values('c-Rahul.Soni','active');
insert into active_engineer(name,status) values('c-Kuldeep.Mehta','active');
insert into active_engineer(name,status) values('c-Rajesh.Chauhan','active');
insert into active_engineer(name,status) values('c-shyam.nishad','active');
insert into active_engineer(name,status) values('c-Vineet.Tomar','active');
insert into active_engineer(name,status) values('c-Mohit.Chandila','active');
insert into active_engineer(name,status) values('c-Devendra.Bhatt','active');
insert into active_engineer(name,status) values('c-Jay.P','active');
insert into active_engineer(name,status) values('c-Himanshu.Singh','active');
insert into active_engineer(name,status) values('c-Durgesh.Singh','active');
insert into active_engineer(name,status) values('c-Naveen.Rana','active');
insert into active_engineer(name,status) values('chanakya.neeraj','active');
insert into active_engineer(name,status) values('neeraj.kumar2','active');
insert into active_engineer(name,status) values('Salman.Ahmed','active');
insert into active_engineer(name,status) values('Vijay.Negi','active');
insert into active_engineer(name,status) values('subodh.mamgain','active');
insert into active_engineer(name,status) values('c-Aviral.Gupta','active');
insert into active_engineer(name,status) values('Parmanand.Tiwari','active');
insert into active_engineer(name,status) values('c-Shoeb.Khan','active');
insert into active_engineer(name,status) values('Pradeep.Kumar1','active');
insert into active_engineer(name,status) values('Panditaray.Madditot','active');
insert into active_engineer(name,status) values('c-Ravi.Pandey','active');
insert into active_engineer(name,status) values('c-Gaurav.khurana','active');
create database keystonesrvack;
CREATE TABLE keystonesrvack.keystoneserverack SELECT * FROM live_sysadmin.ticketdetails;
mysql -udb_user -p'S3curity@@' -h 172.29.102.103
CREATE TABLE org_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE helpdesk_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE gsuite_cost (
id int(11) not null auto_increment,
category varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
cost decimal(12,2) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
CREATE TABLE track_ticket_move (
id int(11) not null auto_increment,
created_time int(10),
started_time int(10),
hold_start_time int(10),
hold_end_time int(10),
hold_count int(5),
current_status varchar(15),
ticket int(10),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;
alter table track_ticket_move modify current_status varchar(25);
==>
RT database, database postgresql->
CREATE TABLE track_ticket_move (
id integer primary key ,
created_time integer,
started_time integer,
hold_start_time integer,
hold_end_time integer,
hold_count integer,
current_status varchar(25),
ticket integer
);
CREATE INDEX ticket_index on track_ticket_move (ticket);
----------------------------------------
grant select on all tables in schema public to "svc.rt16";
grant select on all tables in schema public to devendrak;
grant all privileges on all tables in schema public to rtappusr;
==<
=============== ============== ===>>>>>>>>>>>
==========
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 , (EXTRACT(EPOCH FROM t1.started)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0) - EXTRACT(EPOCH FROM created)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0))/60::integer, case when ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)))/60 )::integer>30 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0) )- (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>45 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>90 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) )/60 )::integer>105 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time)/60)::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;
drop 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 ,
(EXTRACT(EPOCH FROM t1.started)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0) - EXTRACT(EPOCH FROM created)+coalesce(sum(t2.hold_end_time-t2.hold_start_time)::integer,0))/60::integer,
case when ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)))/60 )::integer>30 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0) )- (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>45 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>90 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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 ( ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) )/60 )::integer>105 and ( ( (EXTRACT(EPOCH FROM t1.started)+coalesce((sum(t2.hold_end_time-t2.hold_start_time))::integer,0)) - (EXTRACT(EPOCH FROM t1.created)+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;
=============>
DARWIN DATA TABLE
=================>
CREATE TABLE darwindata_new (
id int(11) NOT NULL DEFAULT '0',
employee_id int(11) NOT NULL,
first_name varchar(70) NOT NULL,
last_name varchar(20) NOT NULL,
sap_id int(11) NOT NULL,
company_email_id varchar(60) DEFAULT NULL,
designation varchar(60) DEFAULT NULL,
department varchar(60) DEFAULT NULL,
departments_hierarchy varchar(50) DEFAULT NULL,
office_area varchar(80) DEFAULT NULL,
office_city varchar(30) DEFAULT NULL,
office_state varchar(30) DEFAULT NULL,
office_country varchar(30) DEFAULT NULL,
date_of_joining varchar(50) DEFAULT NULL,
direct_manager_employee_id int(11) DEFAULT NULL,
hod varchar(30) DEFAULT NULL,
job_level varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
<===================
<==================
create view darwindata_master_new as
select e.company_email_id as employee,e.job_level as empjob, lvl1.company_email_id as lvl1email,lvl1.job_level as lvljobid,lvl2.job_level lvl2jobid,lvl2.company_email_id as lvl2email,lvl2.employee_id
from darwindata e
inner join darwindata lvl1 on e.direct_manager_employee_id = lvl1.employee_id
inner join darwindata lvl2 on lvl1.direct_manager_employee_id = lvl2.employee_id;
<================
<===============
Comments
Post a Comment