# Fórmulas e queries para indicadores SEFAZ - N1/Produção e monitoramento / DEVOPS

## XGnPM

### IG1PM - Mensal - SQL NOVO
$$
\frac{IncidentesTSIx – IncidentesTSIxEncerradosAtraso}{IncidentesTSIx}
$$
- Tipo = Incidente
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSI1, TSI2, TSI3, TSI4
- Encerrados com atraso = campo `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `MDB_SOLUTIS`:
- `CHAMADO`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- `COD_SOLUCAO = Baseado na Prioridade`
- `GRUPO_SOLUCIONADOR = PRODUÇÃO, NOC`
- `TIPO_CHAMADO = I`

### IG1PM - Mensal - SQL NOVO

-- IG1PM
-- CTE_INCIDENTE_SLA_CICLO
WITH
CTE_INCIDENTE_SLA_CICLO AS (
   SELECT
        cr.ref_num AS [CHAMADO],
        SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
        cr.sla_violation AS [SLA_VIOLADO_CA],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO]
   FROM
        call_req cr 
        JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN pri ON cr.priority = pri.enum
   WHERE
       cr.type = 'I'
       AND pri.sym IN ('P1', 'P2', 'P3', 'P4')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   pri.sym,
   cr.sla_violation
),

-- CTE_CHAMADO_SLA_CR
CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (incidentes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_INCIDENTE_SLA_CICLO incidentes ON cr.ref_num = incidentes.[CHAMADO]
    WHERE   
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
        AND (cr.status = 'CL' AND cr.status != 'CNCL')
        AND cr.type = 'I'
        AND incidentes.[COD_SOLUCAO] IN ('TSI1', 'TSI2', 'TSI3', 'TSI4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        incidentes.[TOTAL_SOLUTION_TIME],
        incidentes.[COD_SOLUCAO]
),

-- IG1PM - Mensal
CTE_Chamados AS (
    SELECT
        cr.ref_num AS [CHAMADO],
        CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO],
        CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR

    FROM
        call_req cr 
        JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN pri ON cr.priority = pri.enum
        LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
    WHERE
        (cr.type = 'I' AND cr.status = 'CL' )
        AND pri.sym IN ('P1', 'P2', 'P3', 'P4')
        AND (grp.contact_type = 2308)
        AND grp.last_name IN ('PRODUÇÃO', 'NOC')
        AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSI1'), ('TSI2'), ('TSI3'), ('TSI4')) AS T(COD_SOLUCAO)
)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS [COD_SOLUCAO],
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS ATENDIDOS,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS [TOTAL],
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS IG1PM  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_Chamados AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;

### IG2PM - Mensal - SQL NOVO
$$
\frac{IncidentesTSIx – IncidentesTSIxEncerradosAtraso}{IncidentesTSIx}
$$
- Tipo = Incidente
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSI5, TSI6, TSI7
- Encerrados com atraso = `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `MDB_SOLUTIS`:
- `CHAMADO`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- `COD_SOLUCAO = Baseado na Prioridade`
- `GRUPO_SOLUCIONADOR = PRODUÇÃO, NOC`
- `TIPO_CHAMADO = I`

### IG2PM - Mensal - SQL NOVO

-- IG2PM - Mensal
-- CTE_INCIDENTE_SLA_CICLO
WITH
CTE_INCIDENTE_SLA_CICLO AS (
   SELECT
        cr.ref_num AS [CHAMADO],
        SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
        cr.sla_violation AS [SLA_VIOLADO_CA],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO]
   FROM
        call_req cr 
        JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN pri ON cr.priority = pri.enum
   WHERE
       cr.type = 'I'
       AND pri.sym IN ('P5', 'P6', 'P7')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   pri.sym,
   cr.sla_violation
),

-- CTE_CHAMADO_SLA_CR
CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (incidentes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_INCIDENTE_SLA_CICLO incidentes ON cr.ref_num = incidentes.[CHAMADO]
    WHERE   
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
        AND (cr.status = 'CL' AND cr.status != 'CNCL')
        AND cr.type = 'I'
        AND incidentes.[COD_SOLUCAO] IN ('TSI5', 'TSI6', 'TSI7')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        incidentes.[TOTAL_SOLUTION_TIME],
        incidentes.[COD_SOLUCAO]
),

-- IG2PM - Mensal
CTE_Chamados AS (
    SELECT
        cr.ref_num AS [CHAMADO],
        CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO],
        CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR

    FROM
        call_req cr 
        JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN pri ON cr.priority = pri.enum
        LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
    WHERE
        (cr.type = 'I' AND cr.status = 'CL' )
        AND pri.sym IN ('P5', 'P6', 'P7')
        AND (grp.contact_type = 2308)
        AND grp.last_name IN ('PRODUÇÃO', 'NOC')
        AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSI5'), ('TSI6'), ('TSI7')) AS T(COD_SOLUCAO)
)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS [COD_SOLUCAO],
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS ATENDIDOS,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS [TOTAL],
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS IG2PM  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_Chamados AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;

### SG1PM - Mensal - SQL NOVO
$$
\frac{SolicitaçõesTSSx – SolicitaçõesTSSxEncerradosAtraso}{SolicitaçõesTSSx}
$$
- Tipo = Solicitação
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSS1, TSS2, TSS3, TSS4
- Encerrados com atraso = `SLA_VIOLADO = 1`
- Campos utilizados dentro da base `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da base `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da base `CTE_Chamados_RDM_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `TAREFA_SEQUENCE = Sequência da tarefa no chamado`
- `TAREFA_NOME = Desrição da tarefa no chamado`
- `TAREFA_GRUPO = GRUPO da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

### SG1PM - Mensal - SQL NOVO

-- ### SG1PM
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND crwf.status='COMP'
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY crwf.object_id ,crwf.id, crwf.sequence, crwf.task , crwf.description ,cnt.last_name, cr.ref_num, z_TPS.code
),


CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),


CTE_CHAMADO_SLA_CR_WF AS (
   SELECT 
        cr.ref_num AS [CHAMADO],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        CASE WHEN (solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_TAREFAS_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        solicitacoes.[COD_SOLUCAO]
),

-- SG1N3 - Mensal
CTE_Chamados AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

--OBTEM TODAS AS TAREFAS DO CHAMADO QUE VALEM COMO CHAMADO
CTE_Chamados_CR_WF AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CS.TAREFA_ID as [TAREFA_ID],
		CS.[SEQUENCE],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR_WF
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN CTE_CHAMADO_SLA_CR_WF CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

-- RDM x TAREFAS
CTE_Chamados_RDM_WF AS (
	SELECT
		chg.chg_ref_num as [CHAMADO],
		wf.id as [TAREFA_ID],
		wf.sequence as [TAREFA_SEQUENCE],
		wf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4) AS [COD_SOLUCAO],
		CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
			WHEN 'TSS1' THEN 1800  -- 30Min
			WHEN 'TSS2' THEN 3600  -- 1H
			WHEN 'TSS3' THEN 7200  -- 2H
			WHEN 'TSS4' THEN 14400 -- 4H
			-- Leva em consideração valores do documento INDICADORES
        ELSE 0
		END AS [SLA_EM_SEGUNDOS],
		CASE 
        WHEN SUM(z_ciclo_task.tempo_sla) > 
             CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
                 WHEN 'TSS1' THEN 1800
                 WHEN 'TSS2' THEN 3600
                 WHEN 'TSS3' THEN 7200
                 WHEN 'TSS4' THEN 14400
                 ELSE 0
             END 
        THEN 1 
        ELSE 0 
    END AS [VIOLACAO_SLA_NOVO]

	FROM wf 
		JOIN z_ciclo_task ON z_ciclo_task.wf_id = wf.id
		JOIN ca_contact cnt on wf.group_id = cnt.contact_uuid
		JOIN chg ON wf.object_id = chg.id
		JOIN srv_desc sdsc ON wf.support_lev = sdsc.persid
		LEFT JOIN ATTACHED_SLA sla ON wf.id = sla.ticket_id AND sla.ticket_type = 'wf'
		--JOIN chgcat pcat ON pcat.code = chg.category NÃO USAMOS COD_SOLUCAO categoria, porque NAO EXISTE COD ATRELADO

	WHERE  
		(  cnt.contact_type = 2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND (wf.status='COMP' AND wf.object_type='chg')
		AND (sdsc.sym like '%TSS1' OR sdsc.sym like '%TSS2'OR sdsc.sym like '%TSS3' OR sdsc.sym like '%TSS4')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY wf.object_id ,wf.id  ,wf.task ,wf.description ,cnt.last_name, chg.chg_ref_num, sdsc.sym, wf.id, wf.sequence
),


All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSS1'), ('TSS2'), ('TSS3'), ('TSS4')) AS T(COD_SOLUCAO)
),

-- Combina os chamados de SOLICITAÇÕES E TAREFAS_SOLICITACOES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_CR_WF --Tarefas dos chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_RDM_WF --Tarefas das RDMs

)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS priority,
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS fulfilled,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS total,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS result  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_CHAMADOS_COMBINADOS AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;

### SG2PM - Mensal - SQL NOVO
$$
\frac{SolicitaçõesTSSx – SolicitaçõesTSSxEncerradosAtraso}{SolicitaçõesTSSx}
$$
- Tipo = Solicitação
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSS5, TSS6, TSS7, TSS8, TSS9, TSS10
- Encerrados com atraso = campo `SLA_VIOLADO = 1`
- Campos utilizados dentro da CTE `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_RDM_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `TAREFA_SEQUENCE = Sequência da tarefa no chamado`
- `TAREFA_NOME = Desrição da tarefa no chamado`
- `TAREFA_GRUPO = GRUPO da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

### SG2PM - Mensal - SQL NOVO

-- ### SG2PM
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND crwf.status='COMP'
		AND z_TPS.code IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY crwf.object_id ,crwf.id, crwf.sequence, crwf.task , crwf.description ,cnt.last_name, cr.ref_num, z_TPS.code
),


CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),


CTE_CHAMADO_SLA_CR_WF AS (
   SELECT 
        cr.ref_num AS [CHAMADO],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        CASE WHEN (solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_TAREFAS_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        solicitacoes.[COD_SOLUCAO]
),

-- SG1N3 - Mensal
CTE_Chamados AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

--OBTEM TODAS AS TAREFAS DO CHAMADO QUE VALEM COMO CHAMADO
CTE_Chamados_CR_WF AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CS.TAREFA_ID as [TAREFA_ID],
		CS.[SEQUENCE],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR_WF
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN CTE_CHAMADO_SLA_CR_WF CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

-- RDM x TAREFAS
CTE_Chamados_RDM_WF AS (
	SELECT
		chg.chg_ref_num as [CHAMADO],
		wf.id as [TAREFA_ID],
		wf.sequence as [TAREFA_SEQUENCE],
		wf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4) AS [COD_SOLUCAO],
			CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
				WHEN 'TSS5' THEN 21600 -- 6H
				WHEN 'TSS6' THEN 28800 -- 8H
				WHEN 'TSS7' THEN 43200 -- 12H
				WHEN 'TSS8' THEN 57600 -- 16H
				WHEN 'TSS9' THEN 72000 -- 20H
			-- Leva em consideração valores do documento INDICADORES
			ELSE 0
			END AS [SLA_EM_SEGUNDOS],
		CASE 
        WHEN SUM(z_ciclo_task.tempo_sla) > 
			CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
				WHEN 'TSS5' THEN 21600 -- 6H
				WHEN 'TSS6' THEN 28800 -- 8H
				WHEN 'TSS7' THEN 43200 -- 12H
				WHEN 'TSS8' THEN 57600 -- 16H
				WHEN 'TSS9' THEN 72000 -- 20H
            ELSE 0
            END 
        THEN 1 
        ELSE 0 
    END AS [VIOLACAO_SLA_NOVO]

	FROM wf 
		JOIN z_ciclo_task ON z_ciclo_task.wf_id = wf.id
		JOIN ca_contact cnt on wf.group_id = cnt.contact_uuid
		JOIN chg ON wf.object_id = chg.id
		JOIN srv_desc sdsc ON wf.support_lev = sdsc.persid
		LEFT JOIN ATTACHED_SLA sla ON wf.id = sla.ticket_id AND sla.ticket_type = 'wf'
	WHERE  
		(  cnt.contact_type = 2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND (wf.status='COMP' AND wf.object_type='chg')
		AND (sdsc.sym like '%TSS5' OR sdsc.sym like '%TSS6'OR sdsc.sym like '%TSS7' OR sdsc.sym like '%TSS8' OR sdsc.sym like '%TSS9')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY wf.object_id ,wf.id  ,wf.task ,wf.description ,cnt.last_name, chg.chg_ref_num, sdsc.sym, wf.id, wf.sequence
),
All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSS5'), ('TSS6'), ('TSS7'), ('TSS8'), ('TSS9')) AS T(COD_SOLUCAO)
),

-- Combina os chamados de SOLICITAÇÕES E TAREFAS_SOLICITACOES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_CR_WF --Tarefas dos chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_RDM_WF --Tarefas das RDMs

)

SELECT
   A.COD_SOLUCAO AS priority,
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS fulfilled,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS total,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS result  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_CHAMADOS_COMBINADOS AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;

### RG1PM - Mensal - SQL NOVO
$$
\frac{RotinasTSRx – RotinasTSRxEncerradosAtraso}{RotinasTSRx}
$$
- Tipo = Rotina
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSR1, TSR2, TSR3
- Encerrados com atraso = `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da base `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

### RG1PM - Mensal - SQL NOVO

-- ### RG1PM
-- DEMOROU 20 MINUTOS EXECUÇÃO
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSR1', 'TSR2', 'TSR3')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND crwf.status='COMP'
		AND z_TPS.code IN ('TSR1', 'TSR2', 'TSR3')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY crwf.object_id ,crwf.id, crwf.sequence, crwf.task , crwf.description ,cnt.last_name, cr.ref_num, z_TPS.code
),


CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSR1', 'TSR2', 'TSR3')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),


CTE_CHAMADO_SLA_CR_WF AS (
   SELECT 
        cr.ref_num AS [CHAMADO],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        CASE WHEN (solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_TAREFAS_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSR1', 'TSR2', 'TSR3')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        solicitacoes.[COD_SOLUCAO]
),

-- RG1PM - Mensal
CTE_Chamados AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSR1', 'TSR2', 'TSR3')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

--OBTEM TODAS AS TAREFAS DO CHAMADO QUE VALEM COMO CHAMADO
CTE_Chamados_CR_WF AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CS.TAREFA_ID as [TAREFA_ID],
		CS.[SEQUENCE],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR_WF
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN CTE_CHAMADO_SLA_CR_WF CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSR1', 'TSR2', 'TSR3')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),



All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSR1'), ('TSR2'), ('TSR3')) AS T(COD_SOLUCAO)
),

-- Combina os chamados de SOLICITAÇÕES E TAREFAS_SOLICITACOES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados -- CHAMADOS do tipo Rotina
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_CR_WF --Tarefas dos chamados que conta como chamado

)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS [COD_SOLUCAO],
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS [ATENDIDOS],  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS total,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS [RESULTADO]  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_CHAMADOS_COMBINADOS AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;
GO

### RG2PM - Mensal - SQL NOVO
$$
\frac{RotinasTSRx – RotinasTSRxEncerradosAtraso}{RotinasTSRx}
$$
- Tipo = Rotina
- Status = Fechada
- Grupo Solucionador = PRODUÇÃO e NOC
- Grupo de prioridades = TSR4, TSR5
- Encerrados com atraso = `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da base `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

-- ### RG2PM
-- DEMOROU 20 MINUTOS EXECUÇÃO
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSR4', 'TSR5')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('PRODUÇÃO', 'NOC') )
		AND crwf.status='COMP'
		AND z_TPS.code IN ('TSR4', 'TSR5')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY crwf.object_id ,crwf.id, crwf.sequence, crwf.task , crwf.description ,cnt.last_name, cr.ref_num, z_TPS.code
),


CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSR4', 'TSR5')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),


CTE_CHAMADO_SLA_CR_WF AS (
   SELECT 
        cr.ref_num AS [CHAMADO],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        CASE WHEN (solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_TAREFAS_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSR4', 'TSR5')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        solicitacoes.[COD_SOLUCAO]
),

-- RG2PM - Mensal
CTE_Chamados AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSR4', 'TSR5')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

--OBTEM TODAS AS TAREFAS DO CHAMADO QUE VALEM COMO CHAMADO
CTE_Chamados_CR_WF AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CS.TAREFA_ID as [TAREFA_ID],
		CS.[SEQUENCE],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR_WF
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN CTE_CHAMADO_SLA_CR_WF CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSR4', 'TSR5')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('PRODUÇÃO', 'NOC')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSR4'), ('TSR5')) AS T(COD_SOLUCAO)
),

-- Combina os chamados de SOLICITAÇÕES E TAREFAS_SOLICITACOES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados -- CHAMADOS do tipo Rotina
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_CR_WF --Tarefas dos chamados que conta como chamado

)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS priority,
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS fulfilled,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS total,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS result  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_CHAMADOS_COMBINADOS AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;
GO

## XGnDS

### IG1DS - Mensal NOVO
$$
\frac{IncidentesTSIx – IncidentesTSIxEncerradosAtraso}{IncidentesTSIx}
$$
- Tipo = Incidente
- Status = Fechada
- Grupo Solucionador = 'GEPIN AS Atendimento'
- Grupo de prioridades = TSI1, TSI2, TSI3, TSI4
- Encerrados com atraso = `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `MDB_SOLUTIS`:
- `CHAMADO`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- `COD_SOLUCAO = Baseado na Prioridade`
- `GRUPO_SOLUCIONADOR = GEPIN AS Atendimento`
- `TIPO_CHAMADO = I`

> sql NOVO

-- IG1DS
-- CTE_INCIDENTE_SLA_CICLO
WITH
CTE_INCIDENTE_SLA_CICLO AS (
   SELECT
        cr.ref_num AS [CHAMADO],
        SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
        cr.sla_violation AS [SLA_VIOLADO_CA],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO]
   FROM
        call_req cr 
        JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN pri ON cr.priority = pri.enum
   WHERE
       cr.type = 'I'
       AND pri.sym IN ('P1', 'P2', 'P3', 'P4')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   pri.sym,
   cr.sla_violation
),

-- CTE_CHAMADO_SLA_CR
CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (incidentes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_INCIDENTE_SLA_CICLO incidentes ON cr.ref_num = incidentes.[CHAMADO]
    WHERE   
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
        AND (cr.status = 'CL' AND cr.status != 'CNCL')
        AND cr.type = 'I'
        AND incidentes.[COD_SOLUCAO] IN ('TSI1', 'TSI2', 'TSI3', 'TSI4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        incidentes.[TOTAL_SOLUTION_TIME],
        incidentes.[COD_SOLUCAO]
),

-- IG1DS - Mensal
CTE_Chamados AS (
    SELECT
        cr.ref_num AS [CHAMADO],
        CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO],
        CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR

    FROM
        call_req cr 
        JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN pri ON cr.priority = pri.enum
        LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
    WHERE
        (cr.type = 'I' AND cr.status = 'CL' )
        AND pri.sym IN ('P1', 'P2', 'P3', 'P4')
        AND (grp.contact_type = 2308)
        AND grp.last_name IN ('GEPIN AS Atendimento')
        AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
),

All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSI1'), ('TSI2'), ('TSI3'), ('TSI4')) AS T(COD_SOLUCAO)
)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS [COD_SOLUCAO],
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS ATENDIDOS,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS [TOTAL],
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS IG1DS  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_Chamados AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;
GO

### IG2DS - Mensal NOVO
$$
\frac{IncidentesTSIx – IncidentesTSIxEncerradosAtraso}{IncidentesTSIx}
$$
- Tipo = Incidente
- Status = Fechada
- Grupo Solucionador = 'GEPIN AS Atendimento'
- Grupo de prioridades = TSI5, TSI6, TSI7
- Encerrados com atraso = campo `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da base `MDB_SOLUTIS`:
- `CHAMADO`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- `COD_SOLUCAO = Baseado na Prioridade`
- `GRUPO_SOLUCIONADOR = GEPIN AS Atendimento`
- `TIPO_CHAMADO = I`

> sql NOVO

-- IG2DS - Mensal
-- CTE_INCIDENTE_SLA_CICLO
WITH
CTE_INCIDENTE_SLA_CICLO AS (
   SELECT
        cr.ref_num AS [CHAMADO],
        SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
        cr.sla_violation AS [SLA_VIOLADO_CA],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO]
   FROM
        call_req cr 
        JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN pri ON cr.priority = pri.enum
   WHERE
       cr.type = 'I'
       AND pri.sym IN ('P5', 'P6', 'P7')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   pri.sym,
   cr.sla_violation
),

-- CTE_CHAMADO_SLA_CR
CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (incidentes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_INCIDENTE_SLA_CICLO incidentes ON cr.ref_num = incidentes.[CHAMADO]
    WHERE   
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
        AND (cr.status = 'CL' AND cr.status != 'CNCL')
        AND cr.type = 'I'
        AND incidentes.[COD_SOLUCAO] IN ('TSI5', 'TSI6', 'TSI7')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        incidentes.[TOTAL_SOLUTION_TIME],
        incidentes.[COD_SOLUCAO]
),

-- IG2DS - Mensal
CTE_Chamados AS (
    SELECT
        cr.ref_num AS [CHAMADO],
        CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO],
        CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR

    FROM
        call_req cr 
        JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN pri ON cr.priority = pri.enum
        LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
    WHERE
        (cr.type = 'I' AND cr.status = 'CL' )
        AND pri.sym IN ('P5', 'P6', 'P7')
        AND (grp.contact_type = 2308)
        AND grp.last_name IN ('GEPIN AS Atendimento')
        AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2022-01-01 00:00:00' AND '2024-07-31 23:59:59'
),

All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSI5'), ('TSI6'), ('TSI7')) AS T(COD_SOLUCAO)
)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS [COD_SOLUCAO],
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS ATENDIDOS,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS [TOTAL],
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS IG2DS  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_Chamados AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;
GO

### SG1DS - Mensal
$$
\frac{SolicitaçõesTSSx – SolicitaçõesTSSxEncerradosAtraso}{SolicitaçõesTSSx}
$$
- Tipo = Solicitação
- Status = Fechada
- Grupo Solucionador = 'GEPIN AS Atendimento'
- Grupo de prioridades = TSS1, TSS2, TSS3, TSS4
- Encerrados com atraso = campo `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da CTE `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_RDM_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `TAREFA_SEQUENCE = Sequência da tarefa no chamado`
- `TAREFA_NOME = Desrição da tarefa no chamado`
- `TAREFA_GRUPO = GRUPO da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

> sql

-- ### SG1DS
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('GEPIN AS Atendimento') )
		AND crwf.status='COMP'
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY crwf.object_id ,crwf.id, crwf.sequence, crwf.task , crwf.description ,cnt.last_name, cr.ref_num, z_TPS.code
),


CTE_CHAMADO_SLA_CR AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),


CTE_CHAMADO_SLA_CR_WF AS (
   SELECT 
        cr.ref_num AS [CHAMADO],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        CASE WHEN (solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_TAREFAS_SLA_CICLO solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND cr.type = 'R'
		AND solicitacoes.[COD_SOLUCAO] IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_TAREFA_TEMPO_UTIL],
		solicitacoes.[TAREFA_ID],
		solicitacoes.[SEQUENCE],
        solicitacoes.[COD_SOLUCAO]
),

-- SG1DS - Mensal
CTE_Chamados AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_CR CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('GEPIN AS Atendimento')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

--OBTEM TODAS AS TAREFAS DO CHAMADO QUE VALEM COMO CHAMADO
CTE_Chamados_CR_WF AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CS.TAREFA_ID as [TAREFA_ID],
		CS.[SEQUENCE],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_CR_WF
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN CTE_CHAMADO_SLA_CR_WF CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND z_TPS.code IN ('TSS1', 'TSS2', 'TSS3', 'TSS4')
		AND (grp.contact_type = 2308 )
		AND grp.last_name IN ('GEPIN AS Atendimento')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

-- RDM x TAREFAS
CTE_Chamados_RDM_WF AS (
	SELECT
		chg.chg_ref_num as [CHAMADO],
		wf.id as [TAREFA_ID],
		wf.sequence as [TAREFA_SEQUENCE],
		wf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4) AS [COD_SOLUCAO],
		CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
			WHEN 'TSS1' THEN 1800  -- 30Min
			WHEN 'TSS2' THEN 3600  -- 1H
			WHEN 'TSS3' THEN 7200  -- 2H
			WHEN 'TSS4' THEN 14400 -- 4H
			-- Leva em consideração valores do documento INDICADORES
        ELSE 0
		END AS [SLA_EM_SEGUNDOS],
		CASE 
        WHEN SUM(z_ciclo_task.tempo_sla) > 
             CASE SUBSTRING(sdsc.sym, CHARINDEX('TSS', sdsc.sym), 4)
                 WHEN 'TSS1' THEN 1800
                 WHEN 'TSS2' THEN 3600
                 WHEN 'TSS3' THEN 7200
                 WHEN 'TSS4' THEN 14400
                 ELSE 0
             END 
        THEN 1 
        ELSE 0 
    END AS [VIOLACAO_SLA_NOVO]

	FROM wf 
		JOIN z_ciclo_task ON z_ciclo_task.wf_id = wf.id
		JOIN ca_contact cnt on wf.group_id = cnt.contact_uuid
		JOIN chg ON wf.object_id = chg.id
		JOIN srv_desc sdsc ON wf.support_lev = sdsc.persid
		LEFT JOIN ATTACHED_SLA sla ON wf.id = sla.ticket_id AND sla.ticket_type = 'wf'
		--JOIN chgcat pcat ON pcat.code = chg.category NÃO USAMOS COD_SOLUCAO categoria, porque NAO EXISTE COD ATRELADO

	WHERE  
		(  cnt.contact_type = 2308 AND cnt.last_name IN ('GEPIN AS Atendimento') )
		AND (wf.status='COMP' AND wf.object_type='chg')
		AND (sdsc.sym like '%TSS1' OR sdsc.sym like '%TSS2'OR sdsc.sym like '%TSS3' OR sdsc.sym like '%TSS4')
		AND DATEADD(ss, z_ciclo_task.dt_fim - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY wf.object_id ,wf.id  ,wf.task ,wf.description ,cnt.last_name, chg.chg_ref_num, sdsc.sym, wf.id, wf.sequence
),
All_Solutions AS (
     SELECT COD_SOLUCAO
     FROM (VALUES ('TSS1'), ('TSS2'), ('TSS3'), ('TSS4')) AS T(COD_SOLUCAO)
),

-- Combina os chamados de SOLICITAÇÕES E TAREFAS_SOLICITACOES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_CR_WF --Tarefas dos chamados
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_RDM_WF --Tarefas das RDMs

)

-- Consulta final usando a nova coluna de SLA
SELECT
   A.COD_SOLUCAO AS priority,
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS fulfilled,  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS total,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS result  -- Utilizando a nova coluna
FROM All_Solutions AS A
LEFT JOIN CTE_CHAMADOS_COMBINADOS AS C ON A.COD_SOLUCAO = C.COD_SOLUCAO
GROUP BY A.COD_SOLUCAO
ORDER BY A.COD_SOLUCAO;

### SG2DS - Mensal NOVO
$$
\frac{SolicitaçõesTSSx – SolicitaçõesTSSxEncerradosAtraso}{SolicitaçõesTSSx}
$$
- Tipo = Solicitação
- Status = Fechada
- Grupo Solucionador = 'GEPIN AS Atendimento'
- Grupo de prioridades = TSS5, TSS6, TSS7, TSS8, TSS9, TSS10
- Encerrados com atraso = campo `VIOLACAO_SLA_NOVO = 1`
- Campos utilizados dentro da CTE `CTE_Chamados`:
- `CHAMADO`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_CR_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `SEQUENCE = Sequência da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`
- Campos utilizados dentro da CTE `CTE_Chamados_RDM_WF`:
- `CHAMADO`
- `TAREFA_ID`
- `TAREFA_SEQUENCE = Sequência da tarefa no chamado`
- `TAREFA_NOME = Desrição da tarefa no chamado`
- `TAREFA_GRUPO = GRUPO da tarefa no chamado`
- `COD_SOLUCAO = Baseado na Categoria`
- `VIOLACAO_SLA_NOVO = Flag que faz o somatório do tempo útil e compara com evento do SLA`

> sql

-- ### SG2DS
WITH
-- ciclo de vida dos CHAMADO
CTE_SOLICITACAO_SLA_CICLO AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
   WHERE
       cr.type = 'R'
	   AND z_TPS.code IN ('TSS5', 'TSS6', 'TSS7', 'TSS8', 'TSS9')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

--ciclo de vida das TAREFAS CHAMADOS
CTE_TAREFAS_SLA_CICLO AS (
	SELECT
		cr.ref_num as [CHAMADO],
		crwf.description as [TAREFA_NOME],
		cnt.last_name as [TAREFA_GRUPO],
		crwf.id as [TAREFA_ID],
		crwf.sequence as [SEQUENCE],
		SUM(z_ciclo_task.tempo_sla) as [TOTAL_TAREFA_TEMPO_UTIL],
		z_TPS.code AS [COD_SOLUCAO] -- Informado por antonio para usar a COD_SOLUCAO categoria na tela de tarefa
	FROM crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt on crwf.group_id = cnt.contact_uuid
		JOIN call_req cr ON crwf.object_id = cr.persid
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id

	WHERE  
		(  cnt.contact_type=2308 AND cnt.last_name IN ('

## IRE - Mensal - SQL NOVO

$$
\frac{Eventos – EventosTratadosAtrasoErro}{Eventos}
$$
- Tipo = Incidente
- Status = Fechada
- Grupo Solucionador = NOC
- Categoria = Qualquer uma que contenha a palavra "Monitoramento"
- Encerrados com atraso = tempo até o atendimento maior que 20 minutos
- Bases utilizadas = `dbo.ZCVCR`, `dbo.CHAMADOS`
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `CHAMADO`
- `ID_CICLO`
- `DATA_SOLUCAO`
- `STATUS`
- `CATEGORIA`
- `TIPO_CHAMADO`
- `GRUPO_SOLUCIONADOR`
- Campos utilizados dentro da base `dbo.ZCVCR`:
- `CHAMADO_CICLO`
- `ID_CICLO`
- `ULT_MOD_CICLO`
- `ATIVIDADE_CICLO`
- `GRUPO_CICLO`
- `STATUS_ANTERIOR_CICLO`
- `STATUS_ATUAL_CICLO`

## IRE - Mensal - SQL NOVO

-- IRE MONITORAMENTO
WITH CTE AS (
   SELECT
       cr.ref_num AS [CHAMADO_CICLO],
       MIN(zciclo_cr.last_mod_dt) AS MIN_ULT_MOD_CICLO
   FROM
		z_ciclo_de_vida_cr zciclo_cr
		JOIN call_req cr  ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN ca_contact grp ON grp.contact_uuid = zciclo_cr.z_srl_Group
		JOIN act_type ON act_type.code = zciclo_cr.z_srl_Activity 		
   WHERE 
		DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND grp.last_name = 'NOC'
		AND act_type.sym = 'Atualizar status'
		AND zciclo_cr.z_srl_OldStatus = 'OP' --Aberta
		AND zciclo_cr.z_srl_Status = 'WIP' --Em atendimento
   GROUP BY  cr.ref_num
),

CTE2 AS (
   SELECT
       C.ref_num as [CHAMADO],
       MIN(CTE.MIN_ULT_MOD_CICLO) - MIN(C.open_date) AS TEMPO_ATE_ATENDIMENTO
   FROM call_req C
   JOIN z_ciclo_de_vida_cr Z ON C.persid = Z.z_srl_Persistent_id
   JOIN CTE ON C.ref_num = CTE.CHAMADO_CICLO
   JOIN prob_ctg pcat ON pcat.persid = C.category 
   JOIN ca_contact grp ON grp.contact_uuid = C.group_id
   WHERE DATEADD(ss, C.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
       AND C.STATUS = 'CL'
       AND pcat.sym LIKE '%Monitoramento%'
       AND C.type = 'I'
       AND grp.last_name = 'NOC'
   GROUP BY C.ref_num
)

SELECT
   COUNT(CASE WHEN C.TEMPO_ATE_ATENDIMENTO <= 1200 THEN 1 END) AS ATENDIDOS,
   COUNT(*) AS TOTAL,
   AVG(CASE WHEN C.TEMPO_ATE_ATENDIMENTO <= 1200 THEN 1.0 ELSE 0 END) AS IRE
FROM CTE2 AS C;

## CSE - Mensal - SQL NOVO

$$
\frac{ChamadosExecutadosSemErro}{ChamadosExecutados}
$$
- Tipo = Rotina, Solicitação, Incidente
- Status = Fechada
- Grupo Solucionador = NOC
- Encerrados com atraso = campo `SLA_VIOLADO = 1`
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `SLA_VIOLADO`
- `CHAMADO`
- `DATA_SOLUCAO`
- `GRUPO_SOLUCIONADOR`
- `STATUS`
- `TIPO_CHAMADO`

## CSE - Mensal - SQL NOVO

-- ### CSE - MENSAL
WITH
-- ciclo de vida dos CHAMADO SOLICITAÇÕES/ROTINAS
CTE_SOLICITACAO_SLA_CICLO_SOL AS (
   SELECT
		cr.ref_num AS [CHAMADO],
		SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
		cr.sla_violation AS [SLA_VIOLADO_CA],
		z_TPS.code AS [COD_SOLUCAO]
   FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		JOIN ca_contact grp ON grp.contact_uuid = cr.group_id
   WHERE
       cr.type = 'R'
	   AND grp.last_name = 'NOC'
	   AND (z_TPS.code like 'TSR%' OR z_TPS.code like 'TSS%') -- Pega todos TSS e TSR
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   cr.sla_violation,
   z_TPS.code
),

-- ciclo de vida dos CHAMADO INCIDENTES
CTE_SOLICITACAO_SLA_CICLO_INC AS (
   SELECT
        cr.ref_num AS [CHAMADO],
        SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS [TOTAL_SOLUTION_TIME],
        cr.sla_violation AS [SLA_VIOLADO_CA],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO]
   FROM
        call_req cr 
        JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN pri ON cr.priority = pri.enum
		JOIN ca_contact grp ON grp.contact_uuid = cr.group_id
   WHERE
       cr.type = 'I'
	   AND grp.last_name = 'NOC'
       AND pri.sym IN ('P1','P2','P3','P4','P5', 'P6', 'P7')
       AND (cr.status = 'CL' AND cr.status != 'CNCL')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
   GROUP BY 
   cr.ref_num,
   pri.sym,
   cr.sla_violation
),

-- COMPARA o SOMATORIO DO LOG com o SLA de SOLICITAÇÕES/ROTINAS
CTE_CHAMADO_SLA_SOL AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (solicitacoes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
		JOIN ca_contact grp ON grp.contact_uuid = cr.group_id
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO_SOL solicitacoes ON cr.ref_num = solicitacoes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND grp.last_name = 'NOC'
		AND cr.type = 'R'
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        solicitacoes.[TOTAL_SOLUTION_TIME],
        solicitacoes.[COD_SOLUCAO]
),

-- COMPARA o SOMATORIO DO LOG com o SLA de INCIDENTES
CTE_CHAMADO_SLA_INC AS (
   SELECT DISTINCT 
        cr.ref_num AS [CHAMADO],
        CASE WHEN (incidentes.[TOTAL_SOLUTION_TIME] > slatpl.elapsed) THEN 1 ELSE 0 END AS [VIOLACAO_SLA_NOVO]
    FROM
        call_req cr 
		JOIN ca_contact grp ON grp.contact_uuid = cr.group_id
        JOIN attached_sla ON cr.persid = attached_sla.mapped_cr
        JOIN srv_desc ON attached_sla.map_sdsc = srv_desc.persid
        JOIN att_evt ON cr.persid = att_evt.obj_id
        JOIN evt ON att_evt.event_tmpl = evt.persid
        JOIN slatpl ON srv_desc.persid = slatpl.service_type
        JOIN CTE_SOLICITACAO_SLA_CICLO_INC incidentes ON cr.ref_num = incidentes.[CHAMADO]
    WHERE	
        DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND (cr.status = 'CL' AND cr.status != 'CNCL')
		AND grp.last_name = 'NOC'
		AND cr.type = 'I'
        AND slatpl.object_type = 'cr'
        AND (evt.sym = 'Violação de SLA' AND slatpl.sym = 'Violar') -- nome_evt em template e nome_evt no chamado
    GROUP BY 
        cr.ref_num,
        srv_desc.sym,
        slatpl.elapsed,
        cr.sla_violation,
        incidentes.[TOTAL_SOLUTION_TIME],
        incidentes.[COD_SOLUCAO]
),

CTE_Chamados_SOL AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		z_TPS.code AS [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_SOL
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN z_TPS ON pcat.ztempo_servico = z_TPS.id
		LEFT JOIN CTE_CHAMADO_SLA_SOL CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'R' AND cr.status = 'CL' )
		AND (grp.contact_type = 2308 )
		AND (z_TPS.code like 'TSR%' OR z_TPS.code like 'TSS%') -- Pega todos TSS e TSR
		AND grp.last_name = 'NOC'
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),

CTE_Chamados_INC AS (
	SELECT
		cr.ref_num AS [CHAMADO],
		CASE
			WHEN pri.sym = 'P1' THEN 'TSI1'
			WHEN pri.sym = 'P2' THEN 'TSI2'
			WHEN pri.sym = 'P3' THEN 'TSI3'
			WHEN pri.sym = 'P4' THEN 'TSI4'
			WHEN pri.sym = 'P5' THEN 'TSI5'
			WHEN pri.sym = 'P6' THEN 'TSI6'
			WHEN pri.sym = 'P7' THEN 'TSI7'
		ELSE pri.sym END as [COD_SOLUCAO],
		CS.VIOLACAO_SLA_NOVO  -- Adicionando a coluna VIOLACAO_SLA_NOVO da CTE_CHAMADO_SLA_SOL
	FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN prob_ctg pcat ON cr.category = pcat.persid
		JOIN pri ON cr.priority = pri.enum
		LEFT JOIN CTE_CHAMADO_SLA_INC CS ON cr.ref_num = CS.CHAMADO  -- Associando a nova coluna usando o CHAMADO
	WHERE
		(cr.type = 'I' AND cr.status = 'CL' ) --Tipo incidentes
		AND (grp.contact_type = 2308 )
		AND pri.sym IN ('P1','P2','P3','P4','P5', 'P6', 'P7') --Pega todos os Incidentes, equivale TSI1 ao TSI7
		AND grp.last_name = 'NOC'
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
),


-- Combina os chamados de SOLICITAÇÕES/ROTINAS E INCIDENTES
CTE_CHAMADOS_COMBINADOS AS (
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_SOL -- CHAMADOS do tipo SOLICITAÇÕES/ROTINAS
	UNION ALL
	SELECT CHAMADO, COD_SOLUCAO, VIOLACAO_SLA_NOVO
	FROM CTE_Chamados_INC -- CHAMADOS do tipo INCIDENTES
)

-- Consulta final usando a nova coluna de SLA
SELECT
   C.COD_SOLUCAO AS PRIORIDADE,
   COUNT(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1 END) AS [ATENDIDOS],  -- Utilizando a nova coluna
   COUNT(C.COD_SOLUCAO) AS TOTAL,
   ISNULL(AVG(CASE WHEN C.VIOLACAO_SLA_NOVO = 0 THEN 1.0 ELSE 0 END), 0) AS CSE  -- Utilizando a nova coluna
FROM CTE_CHAMADOS_COMBINADOS C
GROUP BY C.COD_SOLUCAO
ORDER BY C.COD_SOLUCAO;

## IGNC - Mensal - SQL NOVO

$$
\frac{ChamadosResolvidos - ChamadosResolvidosEncAtraso}{ChamadosResolvidos}
$$
- Tipo = Rotina, Solicitação, Incidente
- Status = Fechada
- Grupo Solucionador = N1_SERVICEDESK e todos do N2, exceto N2_CABEAMENTO
- Encerrados com atraso = Soma do campo `TEMPO_SOLUCAO` com resultado superior a 5 horas
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `SLA_VIOLADO`
- `CHAMADO`
- `DATA_SOLUCAO`
- `GRUPO_SOLUCIONADOR`
- `STATUS`
- `TIPO_CHAMADO`
- `TEMPO_SOLUCAO`

> sql
>``` *sql
> WITH TOTAL_SOLUTION_TIME AS (
> SELECT
> CHAMADO,
> SUM(CAST(TEMPO_SOLUCAO AS INT)) AS TOTAL_SOLUTION_TIME
> FROM
> dbo.CHAMADOS
> WHERE
> TIPO_CHAMADO IN ('Incidente', 'Solicitação', 'Rotina')
> AND STATUS = 'Fechada'
> AND (
> GRUPO_SOLUCIONADOR = 'N1_SERVICEDESK'
> OR (GRUPO_SOLUCIONADOR LIKE 'N2_%' AND GRUPO_SOLUCIONADOR != 'N2_CABEAMENTO')
> )
> AND DATEADD(ss, DATA_SOLUCAO - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
> GROUP BY CHAMADO
>),
>CTE_SLA_Violado AS (
> SELECT COUNT(*) AS Count_SLA_Violado
> FROM TOTAL_SOLUTION_TIME AS T
> WHERE T.TOTAL_SOLUTION_TIME > 5 * 60 * 60
>),
>CTE_Total AS (
> SELECT COUNT(*) AS Count_Total
> FROM TOTAL_SOLUTION_TIME
>)
>
>SELECT
> CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado AS ATENDIDOS,
> CTE_Total.Count_Total AS TOTAL,
> (CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado) * 1.0 / NULLIF(CTE_Total.Count_Total, 0) AS IGNC
>FROM CTE_SLA_Violado, CTE_Total;

## IGNC - Mensal - SQL NOVO

-- #IGNC - INCIDENTES,SOLICITACOES E ROTINAS
WITH TOTAL_SOLUTION_TIME_CR AS (
	SELECT
		cr.ref_num as [CHAMADO],
		SUM(zciclo.z_dur_Time_spent_group) as [TOTAL_SOLUTION_TIME]
	FROM
		call_req cr
		JOIN z_ciclo_de_vida_cr zciclo ON cr.persid = zciclo.z_srl_Persistent_id
		LEFT JOIN ca_contact cnt ON cr.group_id = cnt.contact_uuid
	WHERE
		(cr.type IN ('I', 'R')) -- incidente, solicitação, rotina
		AND (cr.status = 'CL')
		AND (cnt.contact_type = 2308 AND (cnt.last_name IN ('N1_SERVICEDESK') OR (cnt.last_name LIKE 'N2_%' AND cnt.last_name != 'N2_CABEAMENTO')))
		AND DATEADD(ss, cr.resolve_date - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
	GROUP BY cr.ref_num
),

TOTAL_SOLUTION_TIME_WF_CR AS (
	SELECT
		crwf.id as [TAREFA_ID],
		SUM(z_ciclo_task.tempo_sla) as [TAREFA_TEMPO_UTIL]
	FROM
		crwf 
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = crwf.id
		JOIN ca_contact cnt ON crwf.group_id = cnt.contact_uuid
	WHERE
		(crwf.status = 'COMP')
		AND (cnt.contact_type = 2308 AND (cnt.last_name IN ('N1_SERVICEDESK') OR (cnt.last_name LIKE 'N2_%' AND cnt.last_name != 'N2_CABEAMENTO')))
		AND DATEADD(ss, crwf.completion_date - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
	GROUP BY crwf.id
),

TOTAL_SOLUTION_TIME_WF_CHG AS (
	SELECT
		wf.id as [TAREFA_ID],
		SUM(z_ciclo_task.tempo_sla) as [TAREFA_TEMPO_UTIL]
	FROM
		wf
		JOIN z_ciclo_task ON z_ciclo_task.crwf_id = wf.id
		JOIN ca_contact cnt ON wf.group_id = cnt.contact_uuid
	WHERE
		(wf.status = 'COMP')
		AND (cnt.contact_type = 2308 AND (cnt.last_name IN ('N1_SERVICEDESK') OR (cnt.last_name LIKE 'N2_%' AND cnt.last_name != 'N2_CABEAMENTO')))
		AND DATEADD(ss, wf.completion_date - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
	GROUP BY wf.id
),

CTE_SLA_Violado AS (
	SELECT 
		COUNT(*) AS Count_SLA_Violado
	FROM (
		-- Somando os tempos de todas as CTEs
		SELECT [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_CR
		UNION ALL
		SELECT [TAREFA_TEMPO_UTIL] AS [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_WF_CR
		UNION ALL
		SELECT [TAREFA_TEMPO_UTIL] AS [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_WF_CHG
	) AS TotalSolutionTimes
	WHERE TotalSolutionTimes.TOTAL_SOLUTION_TIME > 5 * 60 * 60 -- SLA violado
),

CTE_TOTAL AS (
	SELECT 
		COUNT(*) AS COUNT_TOTAL
	FROM (
		-- Contabilizando todos os tempos de solução
		SELECT [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_CR
		UNION ALL
		SELECT [TAREFA_TEMPO_UTIL] AS [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_WF_CR
		UNION ALL
		SELECT [TAREFA_TEMPO_UTIL] AS [TOTAL_SOLUTION_TIME] FROM TOTAL_SOLUTION_TIME_WF_CHG
	) AS TotalSolutionTimes
)

SELECT
	CTE_TOTAL.COUNT_TOTAL - CTE_SLA_Violado.Count_SLA_Violado AS ATENDIDOS,
	CTE_TOTAL.COUNT_TOTAL AS TOTAL,
	(CTE_TOTAL.COUNT_TOTAL - CTE_SLA_Violado.Count_SLA_Violado) * 1.0 / NULLIF(CTE_TOTAL.COUNT_TOTAL, 0) as IGNC
FROM
	CTE_SLA_Violado, CTE_TOTAL;
GO

## PDTC - Mensal

$$
\frac{ChamadasDesistencia}{ChamadasTelefoneChat}
$$
- Bases utilizadas = `qstats_mutano.queue_stats`, `qstats_mutano.qevent`, `qstats_mutano.qname`, `qstats_mutano.qagent`
- Campos utilizados dentro da base `qstats_mutano.queue_stats`:
- `qevent`
- `qagent`
- `qname`
- `EVENT`
- `uniqueid`
- `momento_finalizada_abandonada`
- `info3`
- Campos utilizados dentro da base `qstats_mutano.qevent`:
- `event_id`
- Campos utilizados dentro da base `qstats_mutano.qname`:
- `queue_id`
- Campos utilizados dentro da base `qstats_mutano.qagent`:
- `agent_id`

> sql
>``` *sql
> SELECT
> COUNT(CASE WHEN completadas_abandonadas.uniqueid IS NULL THEN 1 END) AS ATENDIDOS,
> COUNT(ingressadas.uniqueid) AS TOTAL,
> AVG(CASE WHEN completadas_abandonadas.uniqueid IS NOT NULL THEN 1.0 ELSE 0 END) AS PDTC
>FROM (
> SELECT uniqueid, EVENT, info2 AS origem, DATETIME AS momento_entrada, qn.queue AS fila
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE CAST(qs.DATETIME AS DATE) BETWEEN CAST('2023-09-01 00:00:00' AS DATE) AND CAST('2023-12-31 23:59:59' AS DATE)
> AND qs.qevent = 15
> AND qn.queue IN ('0131', '0132')
>) ingressadas
>
>LEFT JOIN (
> SELECT uniqueid, EVENT, DATETIME AS momento_finalizada_abandonada, info3 AS tempo_espera_abandono
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE qs.qevent IN (1)
> AND qn.queue IN ('0131', '0132')
> AND qs.info3 > 15
>) completadas_abandonadas ON (ingressadas.uniqueid = completadas_abandonadas.uniqueid);

## TETC - Mensal

$$
\frac{AtendimentosTelefoneChat – AtendimentosTempoEsperaExcedido}{AtendimentosTelefoneChat}
$$
- Bases utilizadas = `qstats_mutano.queue_stats`, `qstats_mutano.qevent`, `qstats_mutano.qname`, `qstats_mutano.qagent`
- Campos utilizados dentro da base `qstats_mutano.queue_stats`:
- `qevent`
- `qagent`
- `qname`
- `EVENT`
- `uniqueid`
- `momento_finalizada_abandonada`
- `info3`
- Campos utilizados dentro da base `qstats_mutano.qevent`:
- `event_id`
- Campos utilizados dentro da base `qstats_mutano.qname`:
- `queue_id`
- Campos utilizados dentro da base `qstats_mutano.qagent`:
- `agent_id`

> sql
>``` *sql
> SELECT
> COUNT(CASE WHEN atendidas.tempo_espera_atendimento <= 60 THEN 1 END) AS ATENDIDOS,
> COUNT(ingressadas.uniqueid) AS TOTAL,
> AVG(CASE WHEN atendidas.tempo_espera_atendimento <= 60 THEN 1.0 ELSE 0 END) AS TETC
>FROM (
> SELECT uniqueid, EVENT, info2 AS origem, DATETIME AS momento_entrada, qn.queue AS fila
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE CAST(qs.DATETIME AS DATE) BETWEEN CAST('2023-11-01 00:00:00' AS DATE) AND CAST('2023-11-30 23:59:59' AS DATE)
> AND qs.qevent = 15
> AND qn.queue IN ('0131', '0132')
>) ingressadas
>
>LEFT JOIN (
> SELECT uniqueid, EVENT, DATETIME AS momento_atendimento, qa.agent AS atendente, info1 AS >tempo_espera_atendimento
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE qs.qevent = 12
> AND qn.queue IN ('0131', '0132')
>) atendidas ON (ingressadas.uniqueid = atendidas.uniqueid)
>
>LEFT JOIN (
> SELECT uniqueid, EVENT, DATETIME AS momento_finalizada_abandonada, info3 AS tempo_espera_abandono
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE qs.qevent IN (1)
> AND qn.queue IN ('0131', '0132')
> AND qs.info3 > 15
>) completadas_abandonadas ON (ingressadas.uniqueid = completadas_abandonadas.uniqueid)
>WHERE completadas_abandonadas.uniqueid IS NOT NULL OR atendidas.uniqueid IS NOT NULL;

## TMN1 - Mensal

$$
\frac{AtendimentosN1 – AtendimentosN1AcimaTempo}{AtendimentosN1}
$$
- Bases utilizadas = `qstats_mutano.queue_stats`, `qstats_mutano.qevent`, `qstats_mutano.qname`, `qstats_mutano.qagent`
- Campos utilizados dentro da base `qstats_mutano.queue_stats`:
- `qevent`
- `qagent`
- `qname`
- `EVENT`
- `uniqueid`
- `momento_finalizada_abandonada`
- `info3`
- Campos utilizados dentro da base `qstats_mutano.qevent`:
- `event_id`
- Campos utilizados dentro da base `qstats_mutano.qname`:
- `queue_id`
- Campos utilizados dentro da base `qstats_mutano.qagent`:
- `agent_id`

> sql
>``` *sql
> SELECT
> COUNT(CASE WHEN completadas_atendidas.tempo_atendimento <= 1800 THEN 1 END) AS ATENDIDOS,
> COUNT(atendidas.uniqueid) AS TOTAL,
> COUNT(CASE WHEN completadas_atendidas.tempo_atendimento <= 1800 THEN 1 END) * 1.0 / >COUNT(atendidas.uniqueid) AS TMN1
>FROM (
> SELECT uniqueid, EVENT, info2 AS origem, DATETIME AS momento_entrada, qn.queue AS fila
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE CAST(qs.DATETIME AS DATE) BETWEEN CAST('2023-11-01 00:00:00' AS DATE) AND CAST('2023-11-30 23:59:59' AS DATE)
> AND qs.qevent = 15
> AND qn.queue IN ('0131', '0132')
>) ingressadas
>
>LEFT JOIN (
> SELECT uniqueid, EVENT, DATETIME AS momento_atendimento, qa.agent AS atendente, info1 AS tempo_espera_atendimento
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE qs.qevent = 12
> AND qn.queue IN ('0131', '0132')
>) atendidas ON (ingressadas.uniqueid = atendidas.uniqueid)
>
>LEFT JOIN (
> SELECT uniqueid, EVENT, DATETIME AS momento_finalizada_atendida, info2 AS tempo_atendimento
> FROM qstats_mutano.queue_stats qs
> LEFT JOIN qstats_mutano.qevent qe ON (qs.qevent = qe.event_id)
> LEFT JOIN qstats_mutano.qagent qa ON (qs.qagent = qa.agent_id)
> LEFT JOIN qstats_mutano.qname qn ON (qs.qname = qn.queue_id)
> WHERE qs.qevent IN (9, 10)
> AND qn.queue IN ('0131', '0132')
>) completadas_atendidas ON (ingressadas.uniqueid = completadas_atendidas.uniqueid);

## TAN1 - Mensal - SQL ANTIGO

$$
\frac{ChamadosFechadosTransitadosN1 - ChamadosAtendidosAtraso}{ChamadosFechadosTransitadosN1}
$$
- Tipo = Solicitação, Incidente
- Status = Fechada
- Grupo Atendimento = N1_SERVICEDESK
- Atendidos com atraso = Mais de 30 minutos para o antendimento
- Bases utilizadas = `dbo.ZCVCR`, `dbo.CHAMADOS`
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `CHAMADO`
- `ID_CICLO`
- `DATA_SOLUCAO`
- `STATUS`
- `CATEGORIA`
- `TIPO_CHAMADO`
- `GRUPO_ATENDIMENTO`
- `VIP`
- Campos utilizados dentro da base `dbo.ZCVCR`:
- `CHAMADO_CICLO`
- `ID_CICLO`
- `ULT_MOD_CICLO`
- `ATIVIDADE_CICLO`
- `GRUPO_CICLO`
- `STATUS_ANTERIOR_CICLO`
- `STATUS_ATUAL_CICLO`

> sql

## TAN1 - Mensal - SQL NOVO

-- TAN1
WITH CTE AS (
	SELECT
		cr.ref_num AS [CHAMADO_CICLO],
		MIN(zciclo_cr.last_mod_dt) as [MIN_ULT_MOD_CICLO]
	FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN ca_contact grp ON zciclo_cr.z_srl_Group = grp.contact_uuid

	WHERE
	   (cr.status = 'CL' AND cr.status != 'CNCL')
	   AND grp.last_name = 'N1_SERVICEDESK' -- passou N1
	   AND zciclo_cr.z_srl_Activity = 'ST' -- Atualizar Status
	   AND (zciclo_cr.z_srl_OldStatus = 'OP' AND zciclo_cr.z_srl_Status = 'WIP') -- Aberto -> Atendimento
	   AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
	GROUP BY 
	cr.ref_num,
	zciclo_cr.last_mod_dt
),
CTE_VIP AS(
	SELECT
		cnt.contact_uuid as [USER_UUID],
		cnt.last_name as [NOME], 
		cnt.userid as [USER_ID], 
		CASE WHEN (hdn.sym IS NOT NULL) THEN 1 ELSE 0 END as [VIP]
	FROM
		ca_contact cnt 
		LEFT JOIN usp_contact_handling cnth	 ON cnth.contact = cnt.contact_uuid 
		LEFT JOIN usp_special_handling hdn   ON hdn.id = cnth.special_handling 
	WHERE 
		hdn.sym IS NOT NULL 
		AND cnt.inactive=0
),
CTE2 AS (
   SELECT
       C.ref_num as [CHAMADO],
       SUM(Z.z_dur_Time_spent_group) AS [TOTAL_TEMPO_CICLO]
	   ,CASE WHEN (CTE_VIP.USER_ID IS NOT NULL) THEN 1 ELSE 0 END as [VIP]
   FROM call_req C
	   JOIN z_ciclo_de_vida_cr Z ON C.persid = Z.z_srl_Persistent_id
	   JOIN ca_contact grp ON Z.z_srl_Group = grp.contact_uuid
	   JOIN CTE ON C.ref_num = CTE.CHAMADO_CICLO
	   LEFT JOIN CTE_VIP ON C.requested_by = CTE_VIP.USER_UUID
   WHERE 
		DATEADD(ss, C.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND C.STATUS = 'CL'
		AND C.type IN ('I', 'R')
		AND grp.last_name = 'N1_SERVICEDESK' -- passou N1
   GROUP BY 
	C.ref_num
   ,CTE_VIP.USER_ID
)


SELECT
   COUNT(CASE WHEN C.VIP = 0 AND C.TOTAL_TEMPO_CICLO <= 1800 THEN 1
              WHEN C.VIP = 1 AND C.TOTAL_TEMPO_CICLO <= 1200 THEN 1 END) AS ATENDIDOS,
   COUNT(*) AS TOTAL,
   AVG(CASE WHEN C.VIP = 0 AND C.TOTAL_TEMPO_CICLO <= 1800 THEN 1.0
            WHEN C.VIP = 1 AND C.TOTAL_TEMPO_CICLO <= 1200 THEN 1.0 ELSE 0 END) AS TAN1
FROM CTE2 AS C;

## SCN1 - Mensal - SQL NOVO

$$
\frac{ChamadosSolucionadosN1}{ChamadosRegistradosPassíveisN1}
$$
- Tipo = Solicitação, Incidente
- Status = Fechada
- Grupo Atendimento = N1_SERVICEDESK
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `CHAMADO`
- `GRUPO_SOLUCIONADOR`
- `DATA_SOLUCAO`
- `STATUS`
- `CATEGORIA`
- `TIPO_CHAMADO`

> sql NOVO

SELECT
	COUNT(CASE WHEN GRUPO_SOLUCIONADOR = 'N1_SERVICEDESK' THEN 1 END) AS ATENDIDOS,
	COUNT(*) AS TOTAL,
	AVG(CASE WHEN GRUPO_SOLUCIONADOR = 'N1_SERVICEDESK' THEN 1.0 ELSE 0 END) AS SCN1
FROM (
	-- Lista os chamados que o N1_SERVICEDESK tocou (ciclo de vida)
	SELECT DISTINCT
		cr.ref_num as [CHAMADO],
		grp_log.last_name as [GRUPO_ATENDIMENTO],
		grp.last_name as [GRUPO_SOLUCIONADOR]
	FROM
		call_req cr 
		JOIN z_ciclo_de_vida_cr zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
		JOIN ca_contact grp_log ON grp_log.contact_uuid = zciclo_cr.z_srl_Group 
		JOIN prob_ctg pcat ON pcat.persid = cr.category
		JOIN ca_contact grp ON grp.contact_uuid = cr.group_id
	WHERE
		grp_log.last_name IN ('N1_SERVICEDESK') -- N1 tocou no chamado
		AND cr.status = 'CL'
		AND cr.type IN ('I', 'R')
		AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59'
		AND pcat.sym IN
		('ESTAÇÃO DE TRABALHO.DESKTOP.Erro.Mau Funcionamento (Equipamento funcionando mas apresenta algum problema ou lentidão)',
		'ESTAÇÃO DE TRABALHO.NOTEBOOK.Erro.Mau Funcionamento (Equipamento funcionando mas apresenta algum problema ou lentidão)',
		'ESTAÇÃO DE TRABALHO.SOFTWARE APLICATIVO.Erro.Mau Funcionamento do Aplicativo',
		'APLICAÇÕES E SERVIÇOS CORPORATIVOS.SISTEMAS CLIENTE-SERVIDOR.Erro.Falha Individual em Sistema VB',
		'SUPORTE AO USUÁRIO.ORIENTAÇÕES GERAIS.Execução.Esclarecimentos Ao Usuário Final',
		'APLICAÇÕES E SERVIÇOS CORPORATIVOS.IMPRESSÃO.Implementação.Instalação / Configuração em Perfil de Usuário',
		'APLICAÇÕES E SERVIÇOS CORPORATIVOS.SISTEMAS CLIENTE-SERVIDOR.Implementação.Instalação / Atualização de Sistema VB',
		'ESTAÇÃO DE TRABALHO.SISTEMA OPERACIONAL.Administração.Configuração de Sistema Operacional',
		'ESTAÇÃO DE TRABALHO.EMPRÉSTIMO.Administração.Reserva de equipamentos para emprestimo',
		'COLABORAÇÃO.OFFICE 365 (TEAMS, SHAREPOINT, E-MAIL).Execução.Orientação ao usuário final',
		'SEGURANÇA DA INFORMAÇÃO.CERTIFICADO DIGITAL.Implementação.Instalação de E-CPF em Estação / Perfil de Usuário',
		'SUPORTE AO USUÁRIO.ACOMPANHAMENTO TÉCNICO.Administração.Reserva de acompanhamento técnico em reuniões e eventos internos e externos',
		'ESTAÇÃO DE TRABALHO.SOFTWARE APLICATIVO.Implementação.Instalação / Atualização / Configuração / Remoção de Aplicativo')
) AS C;

## PSAU - Mensal - SQL ANTIGO

$$
\frac{RespostasBomOtimo}{Respostas}
$$
- Tipo = Solicitação, Incidente
- Status = Fechada
- Grupo Atendimento = Todos, exceto N2_CABEAMENTO
- Bases utilizadas = `dbo.CHAMADOS`, `dbo.PESQUISA`
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `CHAMADO`
- `GRUPO_SOLUCIONADOR`
- `DATA_SOLUCAO`
- `STATUS`
- `RESPOSTA`
- `TIPO_CHAMADO`
- Campos utilizados dentro da base `dbo.PESQUISA`:
- `CHAMADO`

> sql
>``` *sql
> SELECT
> COUNT(CASE WHEN RESPOSTA IN (30,40) THEN 1 END) AS ATENDIDOS,
> COUNT(*) AS TOTAL,
> AVG(CASE WHEN RESPOSTA IN (30,40) THEN 1.0 ELSE 0 END) AS PSAU
>FROM (
> SELECT DISTINCT CHAMADO
> FROM dbo.CHAMADOS
> WHERE DATEADD(ss, DATA_SOLUCAO - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59' -- DATA PARA VALIDAÇÃO DA CONSULTA, POIS EXISTE POUCOS DADOS NA BASE PARA O MÊS ATUAL/ANTERIOR
> AND STATUS = 'Fechada'
> AND GRUPO_SOLUCIONADOR != 'N2_CABEAMENTO'
> AND TIPO_CHAMADO IN ('Incidente', 'Solicitação')
>) AS C
>INNER JOIN dbo.PESQUISA AS P ON C.CHAMADO = P.CHAMADO;

## PSAU - Mensal - SQL NOVO

SELECT
	COUNT(CASE WHEN PESQUISA.RESPOSTA IN ('Ótimo', 'Bom') THEN 1 END) AS ATENDIDOS,
	COUNT(*) AS TOTAL,
	AVG (CASE WHEN PESQUISA.RESPOSTA IN ('Ótimo', 'Bom') THEN 1.0 ELSE 0 END) AS PSAU

FROM
(
	SELECT 
	cr.ref_num AS [CHAMADO],
	CASE WHEN (cr.type = 'I') THEN 'INCIDENTE' ELSE 'SOLICITACAO/ROTINA' END AS [TIPO],
	cnt.last_name AS [USUARIO],
	grp.last_name AS [GRUPO_SOLUCIONADOR],
	FORMAT(dateadd(ss, cr.open_date - 10800, '1/1/1970'),'dd/MM/yyyy HH:mm:ss') AS [DATA_ABERTURA],
	FORMAT(dateadd(ss, svya.last_mod_dt - 10800, '1/1/1970'),'dd/MM/yyyy HH:mm:ss') AS [DATA_RESPOSTA],
	svy.nx_comment AS [COMENTARIO],
	svyq.txt AS [PERGUNTA],
	svya.txt AS [RESPOSTA]

	FROM call_req cr --JOIN PQ precisa ter respondido a pesquisa
		JOIN survey svy ON svy.object_id=cr.id
		JOIN survey_question svyq ON svy.id=svyq.owning_survey
		JOIN survey_answer svya ON svya.own_srvy_question=svyq.id
		JOIN ca_contact cnt ON svya.last_mod_by=cnt.contact_uuid
		JOIN ca_contact grp ON grp.contact_uuid=cr.group_id
	WHERE 
			svya.selected=1 --RESPOSTA SELECIONADA
			AND (grp.contact_type = 2308 AND grp.last_name LIKE 'N2_%' )
			AND grp.last_name NOT IN ('N2_CABEAMENTO')
			AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2024-01-01 00:00:00' AND '2024-07-31 23:59:59'
			AND cr.status = 'CL'
) AS PESQUISA

## TSSS - Mensal - SQL ANTIGO

$$
\frac{ChamadosSupSistemas - ChamadosSupSistemasEncAtraso }{ChamadosSupSistemas}
$$
- Tipo = Solicitação, Incidente
- Status = Fechada
- Grupo Atendimento = N2_SISTEMAS
- Campos utilizados dentro da base `dbo.CHAMADOS`:
- `CHAMADO`
- `GRUPO_SOLUCIONADOR`
- `DATA_SOLUCAO`
- `STATUS`
- `TEMPO_SOLUCAO`
- `TIPO_CHAMADO`

> sql
>``` *sql
> WITH TOTAL_SOLUTION_TIME AS (
> SELECT
> CHAMADO,
> SUM(CAST(TEMPO_SOLUCAO AS INT)) AS TOTAL_SOLUTION_TIME
> FROM
> dbo.CHAMADOS
> WHERE
> TIPO_CHAMADO IN ('Solicitação', 'Incidente')
> AND STATUS = 'Fechada'
> AND GRUPO_SOLUCIONADOR = 'N2_SISTEMAS'
> AND DATEADD(ss, DATA_SOLUCAO - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
> GROUP BY CHAMADO
>),
>CTE_SLA_Violado AS (
> SELECT COUNT(*) AS Count_SLA_Violado
> FROM TOTAL_SOLUTION_TIME AS T
> WHERE T.TOTAL_SOLUTION_TIME > 2 * 60 * 60
>),
>CTE_Total AS (
> SELECT COUNT(*) AS Count_Total
> FROM TOTAL_SOLUTION_TIME
>)
>SELECT
> CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado AS ATENDIDOS,
> CTE_Total.Count_Total AS TOTAL,
> (CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado) * 1.0 / NULLIF(CTE_Total.Count_Total, 0) AS TSSS
>FROM CTE_SLA_Violado, CTE_Total;

## TSSS - Mensal - SQL NOVO

WITH TOTAL_SOLUTION_TIME AS (
   SELECT
       cr.ref_num as [CHAMADO],
       SUM(CAST(zciclo_cr.z_dur_Time_spent_group AS INT)) AS TOTAL_SOLUTION_TIME
   FROM
		call_req cr 
		JOIN ca_contact grp ON cr.group_id = grp.contact_uuid
		JOIN z_ciclo_de_vida_cr  zciclo_cr ON cr.persid = zciclo_cr.z_srl_Persistent_id
   WHERE
       cr.type IN ('I', 'R')
       AND cr.status = 'CL'
       AND (grp.contact_type = 2308 AND grp.last_name = 'N2_SISTEMAS')
       AND DATEADD(ss, cr.close_date - 10800, '19700101') BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
   GROUP BY cr.ref_num
),
CTE_SLA_Violado AS (
   SELECT COUNT(*) AS Count_SLA_Violado
   FROM TOTAL_SOLUTION_TIME AS T
   WHERE T.TOTAL_SOLUTION_TIME > 2 * 60 * 60
),
CTE_Total AS (
   SELECT COUNT(*) AS Count_Total
   FROM TOTAL_SOLUTION_TIME
)
SELECT
   CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado AS ATENDIDOS,
   CTE_Total.Count_Total AS TOTAL,
   (CTE_Total.Count_Total - CTE_SLA_Violado.Count_SLA_Violado) * 1.0 / NULLIF(CTE_Total.Count_Total, 0) AS TSSS
FROM CTE_SLA_Violado, CTE_Total;