Help to simplify query sql query

You can run a query on another query. For example, assuming you have these two queries, where the second is basically a copy of the first one with one more refinement.

Gross form

Transform:

select
    foo, bar
from
    mulheres
where
    gravida = 1
    and cadastro_ativo = 1
    and... -- 500 condicoes aqui

--------------------------------------

select
    foo, bar, ni
from
    mulheres
    inner join consultas on consultas.mulher = mulheres.id
where
    mulheres.gravida = 1
    and mulheres.cadastro_ativo = 1
    and... -- 500 condicoes aqui
    and consultas.data > getdate()

In:

select
    foo, bar, ni
from
    mulheres inner join consultas on consultas.mulher = mulheres.id
where
    consultas.data > getdate()
    and mulheres.id in (
        select
            foo, bar
        from
            mulheres
        where
            gravida = 1
            and cadastro_ativo = 1
            and... -- 500 condicoes aqui
    )

You'll still have a mountain of code, but it's going to be a smaller mountain than you have today. Disadvantage: complexity at best remains the same, but usually increases.

Elegant shape

Follow the steps below:

• Turn the first query into a View.
• Join this view with the second query, adding only the conditions they do not have in common.
• Encapsulate everything in a stored procedure, to make it easier for the database to optimize queries.
• Do not forget to index the most frequently used fields for your searches.
• Having a programming language up one level in the application, you might be able to transfer some of the logic to the top level.

You can store the result of these queries with With :

with gravidas as (
    select
    l.no_cidadao,
    extract(year from l.dt_cad_individual) ano,
    extract(month from l.dt_cad_individual) mes,
    date_part('year',age(now(), l.dt_cad_individual)) diferenca
    from
    tb_unidade_saude p,
    tb_cds_cidadao_resposta a,
    tb_cds_prof as c,
    tb_pessoa_fisica as d,
    tb_equipe as e,
    (SELECT *,
       ROW_NUMBER() OVER (PARTITION BY no_cidadao
       ORDER BY st_atualizacao desc) AS linha
       FROM tb_cds_cad_individual) l
    WHERE LINHA = 1
    AND l.co_seq_cds_cad_individual = a.co_cds_cad_individual
    and c.nu_cns = d.nu_cns
    and c.co_seq_cds_prof = l.co_cds_prof_cadastrante
    and a.co_pergunta = 19
    and a.st_resposta = 1
    and c.nu_ine = e.nu_ine
    and e.co_unidade_saude = p.co_ator_papel
    and p.nu_cnes like '%".$cnes."%'
    group by
    p.nu_cnes, l.no_cidadao, l.dt_cad_individual, e.no_equipe
    order by
    e.no_equipe, l.no_cidadao, l.dt_cad_individual), 

atendidas as (

    select
p.nu_cnes,
a.no_cidadao,
count(case when(extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a2,
count(case when(extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a1
from
tb_unidade_saude p,
tb_cds_cidadao_resposta e,
tb_cds_prof as g,
tb_equipe as f,
tb_cds_ficha_atend_individual d,
tb_cds_atend_individual b,
(SELECT *,
   ROW_NUMBER() OVER (PARTITION BY no_cidadao
   ORDER BY st_atualizacao desc) AS linha
   FROM tb_cds_cad_individual) a
WHERE LINHA = 1
AND a.co_seq_cds_cad_individual = e.co_cds_cad_individual
and g.co_seq_cds_prof = a.co_cds_prof_cadastrante
and e.co_pergunta = 19
and e.st_resposta = 1
and g.nu_ine = f.nu_ine
and f.co_unidade_saude = p.co_ator_papel
and a.nu_cns_cidadao = b.nu_cartao_sus
and ((extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual.")
or (extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior."))
and p.nu_cnes like '%".$cnes."%'
group by
p.nu_cnes, f.no_equipe, a.no_cidadao
order by
a.no_cidadao)



select * from gravidas;

select * from atendidas;

  

The second query could already be simplified with the results of the first one, but then I need to understand all of your select before