Jump to content
Fórum Script Brasil
  • 0

Soma de dias trabalhados


YuriRodrigues96

Question

Boa tarde!

Estou calculando a produtividade dos técnicos que trabalham na empresa e funciona da seguinte forma:

Cada tipo de atividade tem seu peso;

O dia trabalhado é contado através do campo FimVisita;

O sábado tem peso 0.5 e o restante 1;

Porém não consigo adicionar a soma dos dias distintos trabalhados à consulta.

 

SELECT

tu.IDUsuario,

tu.NomeUsuario AS TECNICO,

SUM(

CASE

WHEN tta.TipoServico IN ('INSTALACAO', 'MIGRACAO', 'ALTERACAO_ENDERECO') THEN 1.0

WHEN tta.TipoServico = 'SUPORTE' THEN 0.67

WHEN tta.TipoServico IN ('SOLICITACAO_SERVICO', 'UPGRADE_DOWNGRADE') THEN 0.80

WHEN tta.TipoServico = 'RETIRADA' AND tfa.Descricao IN ('EQUIPAMENTO DEVOLVIDO C/ FONTE', 'EQUIPAMENTO DEVOLVIDO S/ FONTE') THEN 0.25

WHEN tta.TipoServico = 'SOLICITACAO_SERVICO' AND tta.Descricao = 'RECOLHIMENTO FPD' THEN 0.25

ELSE 0

END

) AS PONTUACAO,

sub.SomaDiasDistintos

FROM

master_adapter_comercial.TB_Atendimento ta

LEFT JOIN master_adapter_comercial.TB_Contrato tc ON tc.IDContrato = ta.IDContrato

LEFT JOIN master_adapter.TB_Usuario tu ON tu.IDUsuario = ta.IDTecnicoVisita

LEFT JOIN master_adapter_comercial.TB_TipoAtendimento tta ON tta.IDTipoAtendimento = ta.IDTipoAtendimento

LEFT JOIN master_adapter_comercial.TB_FechamentoAtendimento tfa ON tfa.IDFechamentoAtendimento = ta.IDFechamentoAtendimento

LEFT JOIN (

SELECT

DISTINCT ta2.IDTecnicoVisita,

tu2.NomeUsuario,

CAST(ta2.DataFimVisita as date) as DataFimVisita,

SUM(CASE WHEN DAYOFWEEK(ta2.DataFimVisita) = 7 THEN 0.5 ELSE 1 END) AS SomaDiasDistintos

FROM

master_adapter_comercial.TB_Atendimento ta2

LEFT JOIN master_adapter.TB_Usuario tu2 ON tu2.IDUsuario = ta2.IDTecnicoVisita

WHERE

ta2.DataFimVisita >= DATE_FORMAT(CURDATE()-1, '%Y%m01')

AND ta2.Status IN ('SOLUCIONADO', 'POS_ATENDIMENTO')

GROUP BY

ta2.IDTecnicoVisita

) AS sub ON sub.IDTecnicoVisita = ta.IDTecnicoVisita

WHERE

ta.DataFimVisita >= DATE_FORMAT(CURDATE() - 1, '%Y%m01')

AND tu.NomeUsuario IS NOT NULL

AND tta.TipoServico <> 'INTERNO'

GROUP BY

sub.NomeUsuario

ORDER BY

tu.NomeUsuario;

Link to comment
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Forum Statistics

    • Total Topics
      152.1k
    • Total Posts
      651.9k
×
×
  • Create New...