Help with MySQL views

sebastian.123456 · 2018-08-31 14:53:11

Good morning community, I would like to know if someone can help me with some views that I must do in mysql (navicat). (select .....)

Thank you for your help, I am in the process of studying mysql views.

* List the number of open and closed requests by a technician.

Example:
open closed
Technician 1 1 10
Technician 2 2 11
Technical 3 1 20

* List Number of requests per applicant

Example:
Department A = 10
Department B = 20
Department C = 0

* List quantity requests by category

Example:
Change of pc = 10
Formatting = 5
Change of account = 6

* List by request of origin
Example

GLPI: 20
Phone: 5
Mail: 2

sebastian.123456 · 2018-09-03 13:15:24

help me please!

LaDenrée · 2018-09-03 18:43:05

for ticket source :

SELECT  COUNT(glpi_tickets.id) AS nbOuverture ,glpi_requesttypes.name AS origine
FROM `glpi_tickets` JOIN glpi_requesttypes ON glpi_requesttypes.id = `glpi_tickets`.`requesttypes_id`
WHERE glpi_tickets.`date`> '2018-03-01' GROUP BY origine

LaDenrée · 2018-09-03 19:59:52

for category stats :

select glpi_itilcategories.name as category, count(glpi_tickets.id) as nombre
 from glpi_tickets 
join glpi_itilcategories on glpi_itilcategories.id=glpi_tickets.itilcategories_id 
where glpi_tickets.date >='2018-01-01' 
group by category

LaDenrée · 2018-09-03 20:01:19

i don't understand "per applicant"...

sebastian.123456 · 2018-09-04 13:34:46

thx you very much friend!

my english is bad. excuse me please.

* List Number requester???

Example:
Department A = 10
Department B = 20
Department C = 0

LaDenrée · 2018-09-04 14:09:00

are departments "requester groups " ?

sebastian.123456 · 2018-09-04 14:42:16

********Hi LaDenrée, the departments are not groups, they are assigned as users "SELF-SERVICE"

* List the number of open and closed requests by a technician.

Example:
open closed
Technician 1 1 10
Technician 2 2 11
Technical 3 1 20

I'm doing this query but I do not have good results

SELECT `glpi_tickets`.`id`,`glpi_users`.`name`,glpi_tickets.status
FROM `glpi_tickets`
LEFT JOIN `glpi_tickets_users` ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` AND `glpi_tickets_users`.`type` = 2 )
LEFT JOIN `glpi_users` ON (`glpi_tickets_users`.`users_id` = `glpi_users`.`id` )
where `glpi_tickets`.`is_deleted` = '0'

LaDenrée · 2018-09-04 15:17:26

try this :

SELECT `glpi_users`.`name`,glpi_tickets.status,count(glpi_tickets.id) as Nombre 
FROM `glpi_tickets` 
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` 
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id` 
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 2 AND glpi_tickets.date>'2018-01-01' 
GROUP BY `glpi_users`.`name`,glpi_tickets.status

LaDenrée · 2018-09-04 15:20:43

i'm not sure I understand " List Number of requests per applicant" but try this

SELECT `glpi_users`.`name` AS Dept ,count(glpi_tickets.id) as Nombre 
FROM `glpi_tickets` 
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` 
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id` 
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 1  AND glpi_tickets.date>'2018-01-01' 
GROUP BY `glpi_users`.`name`

sebastian.123456 · 2018-09-04 15:41:26

Thank you very much, you are an expert. It is what I needed.

How could you give the status a name (close, open, etc.) , for example in the second query.

name status status type tickets
mcano 2    ? 2
mcano 3    ? 1
mcano 6    close 131

LaDenrée · 2018-09-04 15:57:29

SELECT `glpi_users`.`name`,
replace(replace(replace(replace(replace(replace(glpi_tickets.status,"1","new"),"2","Assigned"),"3","planed"),"4","waiting"),"5","solved"),"6","closed") AS statut

,count(glpi_tickets.id) as Nombre 
FROM `glpi_tickets` 
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` 
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id` 
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 2 AND glpi_tickets.date>'2018-01-01' 
GROUP BY `glpi_users`.`name`,statut

sebastian.123456 · 2018-09-04 16:10:19

thank you very much, you are a genius

orthagh · 2018-09-05 08:44:40

For information, @LaDenrée, you can use CASE statement to simplify the query, ex:

CASE
WHEN glpi_tickets.status = 1 THEN "New"
WHEN glpi_tickets.status = 2 THEN "Assigned"
WHEN glpi_tickets.status = 3 THEN "Planed"
WHEN glpi_tickets.status = 4 THEN "Waiting"
WHEN glpi_tickets.status = 5 THEN "Solved"
WHEN glpi_tickets.status = 6 THEN "Closed"
END as status

LaDenrée · 2018-09-05 09:07:26

@orthagh : +1

sebastian.123456 · 2018-09-05 17:54:24

Hello everyone, thanks for all the support provided. Especially to @ LaDenrée.

Is there the possibility of making a large table in mysql view?

For example

id_ticket title Category Technician Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request

THX.

sebastian.123456 · 2018-09-06 16:43:28

It will be very difficult?

LaDenrée · 2018-09-07 00:29:29

this query should work unless you only have 1 requester and 1 technician and 1 group by ticket

SELECT ti.id AS id_ticket,ti.name AS title,cat.name AS category,tech.name AS Technician,ti.priority AS priority,req.name AS requester,
CASE
    WHEN ti.status = 1 THEN "New"
    WHEN ti.status = 2 THEN "Assigned"
    WHEN ti.status = 3 THEN "Planed"
    WHEN ti.status = 4 THEN "Waiting"
    WHEN ti.status = 5 THEN "Solved"
    WHEN ti.status = 6 THEN "Closed"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
	WHEN ti.type = 1 then "Incident" 
    when ti.type = 2 then "request"
END AS ticket_type
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2018-01-01'
GROUP BY  id_ticket,title,category,Technician,priority,requester,status,technician_group,open_date,ticket_source,ticket_type

sebastian.123456 · 2018-09-07 16:08:33

thank you very much friend!!

sebastian.123456 · 2018-09-07 18:53:31

Hello everyone, I have a doubt regarding this row:

id_ticket title Category Technician Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates ** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request
100 Pc repair repair Juan Perez ** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request

Could it be done in this way, so as not to repeat the id_ticket?

id_ticket title Category Technician1 Technician2 Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates** Juan Perez** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request

Thank you very much for your help @La Denrée.

Last edited by sebastian.123456 (2018-09-07 18:54:13)

sebastian.123456 · 2018-09-11 11:48:06

:c

LaDenrée · 2018-09-11 12:26:35

try with

group_concat(tech.name SEPARATOR ' ') AS Technician

sebastian.123456 · 2018-09-11 17:01:49

SELECT ti.id AS id_ticket, ti.name AS title,cat.name AS category,group_concat(tech.name SEPARATOR ' ') AS Technician,ti.priority AS priority,req.name AS requester,
CASE
    WHEN ti.status = 1 THEN "Nuevo"
    WHEN ti.status = 2 THEN "Asignado"
    WHEN ti.status = 3 THEN "Planificado"
    WHEN ti.status = 4 THEN "En espera"
    WHEN ti.status = 5 THEN "Resuelto"
    WHEN ti.status = 6 THEN "Cerrado"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
	WHEN ti.type = 1 then "Incidente" 
    when ti.type = 2 then "Solicitud"
END AS ticket_type
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN glpi_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2016-01-01'
GROUP BY  id_ticket,title,category,Technician,priority,requester,status,technician_group,open_date,ticket_source,ticket_type

Last edited by sebastian.123456 (2018-09-11 17:49:18)

sebastian.123456 · 2018-09-11 17:33:04

SELECT ti.id AS id_ticket, ti.name AS title,cat.name AS category,group_concat(tech.name SEPARATOR ' ') AS Technician,req.name AS requester,
CASE
WHEN ti.status = 1 THEN "Nuevo"
WHEN ti.status = 2 THEN "Asignado"
WHEN ti.status = 3 THEN "Planificado"
WHEN ti.status = 4 THEN "En espera"
WHEN ti.status = 5 THEN "Resuelto"
WHEN ti.status = 6 THEN "Cerrado"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,

CASE
WHEN ti.type = 1 then "Incidente"
when ti.type = 2 then "Solicitud"
END AS ticket_type,

CASE
WHEN ti.priority = 1 THEN "Muy Baja"
WHEN ti.priority = 2 THEN "Baja"
WHEN ti.priority = 3 THEN "Media"
WHEN ti.priority = 4 THEN "Alta"
WHEN ti.priority = 5 THEN "Muy Alta"
WHEN ti.priority = 6 THEN "Cerrado"
END AS prioridad

Could it be divided into two or more tables depending on the number of technicians?

Last edited by sebastian.123456 (2018-09-11 17:37:14)

LaDenrée · 2018-09-11 17:37:48

right, you should remove Technician in group by clause.

SELECT ti.id AS id_ticket,ti.name AS title,cat.name AS category,GROUP_CONCAT(tech.name) AS Technician,ti.priority AS priority,req.name AS requester, 
CASE 
	WHEN ti.`status` = 1 THEN 'New'
    WHEN ti.status = 2 THEN 'Assigned'
    WHEN ti.status = 3 THEN 'Planed'
    WHEN ti.status = 4 THEN 'Waiting'
    WHEN ti.status = 5 THEN 'Solved'
    WHEN ti.status = 6 THEN 'Closed'

END AS status,

sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source, 
CASE
	WHEN ti.type = 1 then 'Incident'  
    WHEN ti.type = 2 then 'request' 

END AS ticket_type 
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN glpi_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2018-01-01'
GROUP BY  id_ticket,title,category,priority,requester,status,technician_group,open_date,ticket_source,ticket_type

and you also should use single quotes in CASE statement

Forum GLPI-Project

Announcement

#1 2018-08-31 14:53:11

Help with MySQL views

#2 2018-09-03 13:15:24

Re: Help with MySQL views

#3 2018-09-03 18:43:05

Re: Help with MySQL views

#4 2018-09-03 19:59:52

Re: Help with MySQL views

#5 2018-09-03 20:01:19

Re: Help with MySQL views

#6 2018-09-04 13:34:46

Re: Help with MySQL views

#7 2018-09-04 14:09:00

Re: Help with MySQL views

#8 2018-09-04 14:42:16

Re: Help with MySQL views

#9 2018-09-04 15:17:26

Re: Help with MySQL views

#10 2018-09-04 15:20:43

Re: Help with MySQL views

#11 2018-09-04 15:41:26

Re: Help with MySQL views

#12 2018-09-04 15:57:29

Re: Help with MySQL views

#13 2018-09-04 16:10:19

Re: Help with MySQL views

#14 2018-09-05 08:44:40

Re: Help with MySQL views

#15 2018-09-05 09:07:26

Re: Help with MySQL views

#16 2018-09-05 17:54:24

Re: Help with MySQL views

#17 2018-09-06 16:43:28

Re: Help with MySQL views

#18 2018-09-07 00:29:29

Re: Help with MySQL views

#19 2018-09-07 16:08:33

Re: Help with MySQL views

#20 2018-09-07 18:53:31

Re: Help with MySQL views

#21 2018-09-11 11:48:06

Re: Help with MySQL views

#22 2018-09-11 12:26:35

Re: Help with MySQL views

#23 2018-09-11 17:01:49

Re: Help with MySQL views

#24 2018-09-11 17:33:04

Re: Help with MySQL views

#25 2018-09-11 17:37:48

Re: Help with MySQL views

Board footer