We help IT Professionals succeed at work.

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

Eduardo Fuerte
on
48 Views
Last Modified: 2019-03-25
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

Open in new window


And dozen of others

Is there a tool to convert it to a diagram?

Thanks in advance
Comment
Watch Question

Database Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Thank you.

I'm convinced this kind of tool inexists.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.