CREATE VIEW V_TRI_GuiasFormacao
AS
SELECT guia.CDU_Numero AS 'NGuia',
       guia.CDU_Cliente AS 'NCliente',
       cliente.Nome AS 'NomeCliente',
       tecnico.Email AS 'EmailTecnico',
       guia.CDU_DataInicio AS 'Data',
       guia.CDU_NHoras AS 'Duracao',
       guia.CDU_Problema AS 'DescricaoProblema',
       guia.CDU_Servico AS 'DescricaoServico',
       condicao.CDU_Descricao AS 'DescricaoCondicao',
       intervencao.CDU_Descricao AS 'DescricaoTipoIntervencao',
       problema.Descricao AS 'DescricaoTipoProblema'
FROM dbo.TDU_GuiasAssistencia guia WITH (NOLOCK)
    LEFT JOIN dbo.Clientes cliente WITH (NOLOCK)
        ON cliente.Cliente = guia.CDU_Cliente
    LEFT JOIN dbo.Tecnicos tecnico WITH (NOLOCK)
        ON tecnico.Tecnico = guia.CDU_Tecnico
    LEFT JOIN dbo.TDU_CondicoesInterv condicao WITH (NOLOCK)
        ON condicao.CDU_Codigo = guia.CDU_Condicoes
    LEFT JOIN dbo.TDU_TipoInterv intervencao WITH (NOLOCK)
        ON intervencao.CDU_Codigo = guia.CDU_TipoInterv
    LEFT JOIN dbo.TiposProblemas problema WITH (NOLOCK)
        ON problema.TipoProblema = guia.CDU_TipoProblema
WHERE guia.CDU_DataInicio >= '2023-01-01'
      AND
      (
          guia.CDU_Servico COLLATE Latin1_general_CI_AI LIKE '%Formação%'
          AND guia.CDU_Servico COLLATE Latin1_general_CI_AI NOT LIKE '%Informação%'
      )
      AND guia.CDU_Condicoes IN ( 0, 1, 2, 3, 5 );