troubleshooting Question

Could you point a tool, considering it exists, that could transform a complex query to a MER diagram by using MSSQLServer?

Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil asked on
Microsoft SQL Server
2 Comments1 Solution57 ViewsLast Modified:
Hi Experts

Could you point a tool, considering it exists, that could transform a complex query to a MER diagram?

Actually I'm doing surveys on a legate system to migrate the data contents to what will be  its correspondents on a CRM.

The queries is quite complex at first view.

SELECT * FROM ( SELECT at.id_atendimento, a.nome_assistido, 
  ( SELECT TOP 1 CONCAT( CASE WHEN c.ddd > 0 THEN CAST(c.ddd AS VARCHAR) COLLATE SQL_Latin1_General_CP850_CI_AI 
      ELSE '' END, c.contato) 
  FROM XXX.dbo.contato as c WHERE c.ativo_sn = 'S' 
    AND c.id_assistido = a.id_assistido 
    AND a.cpf <> CONCAT(CASE WHEN c.ddd > 0 THEN CAST(c.ddd AS VARCHAR) COLLATE SQL_Latin1_General_CP850_CI_AI ELSE '' END, c.contato) 
  ORDER BY ddd DESC, c.principal_sn DESC ) AS contato, 
  ol.descricao AS origem_ligacao, 
  (CASE WHEN a.id_titular IS NULL THEN 'R' ELSE 'D' END) AS tipo_usuario, 
    a.id_titular, at.id_usuario, at.id_servico, at.stamp_inicio_atendimento, 
    at.stamp_fim_atendimento, a.matricula, sni.id_solicitacao_novo_item, sni.id_tipo_status_solicitacao_novo_item, 
    sni.id_tipo_item_finalizado, aa.observacoes, aa.data_agendamento, a.cpf, pat.id_produto, at.atendimento_adverso, 
    at.id_tipo_responsavel, at.id_tipo_relacao, at.nome_responsavel, g.gerenciador, at.id_usuarioematendimento, 
    at.dataematendimento, at.divergencia_farmacia_sn, at.id_ligacao, at.motivo_contato, at.atendimento_em_aberto_sn, 
    at.semcontinuidade_sn, at.id_assistido, at.id_cliente, p.descricao as desc_produto, at.protocolo_atendimento, 
  (SELECT TOP 1 id_tipo_status_solicitacao_novo_item AS ultimo_status FROM XXX.dbo.solicitacao_novo_item AS subSni 
      WHERE subSni.id_atendimento = at.id_atendimento 
  ORDER BY subSni.id_solicitacao_novo_item DESC) AS id_ultimo_status, 
  ROW_NUMBER() OVER( ORDER BY (CASE WHEN aa.data_agendamento < DATEADD(minute,2,GETDATE()) THEN 1 
    WHEN aa.data_agendamento IS NULL THEN 2 ELSE 3 END) ASC, aa.data_agendamento DESC, 
          at.stamp_inicio_atendimento ASC, a.nome_assistido ) AS RowNr FROM XXX.dbo.assistido AS a 
        INNER JOIN XXX.dbo.atendimento AS at ON (at.id_assistido = a.id_assistido) 
        LEFT JOIN XXX.dbo.solicitacao_novo_item AS sni ON (sni.id_atendimento = at.id_atendimento) 
        LEFT JOIN XXX.dbo.gerenciador AS g ON (g.id_gerenciador = at.id_gerenciador) 
        LEFT JOIN XXX.dbo.atendimento_agenda AS aa ON (aa.id_atendimento = at.id_atendimento AND aa.ativo_sn = 'S') 
        LEFT JOIN XXX.dbo.produto_atendimento AS pat ON (pat.id_atendimento = at.id_atendimento) 
        LEFT JOIN XXX.dbo.produtos AS p ON (p.id_produto = pat.id_produto) 
        LEFT JOIN XXX.dbo.origens_ligacao AS ol ON (ol.id_origem_ligacao = at.id_origem_ligacao) 
  WHERE at.ativo_sn = 'S' AND at.atendimento_realizado_sn = 'S' AND at.stamp_inicio_atendimento <= '20190321 23:59:59' AND at.stamp_fim_atendimento IS NULL ) AS tempTable 
  WHERE 1 = 1 AND RowNr BETWEEN 1 AND 20 
  ORDER BY tempTable.id_atendimento DESC, tempTable.i d_solicitacao_novo_item DESC, tempTable.id_tipo_status_solicitacao_novo_item DESC

And dozen of others

Is there a tool to convert it to a diagram?

Thanks in advance
ASKER CERTIFIED SOLUTION
lcohan
Database Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros