COUNT + CASE

18/04/2022

Preciso contar quantidade de pacientes por faixa et�ria, alguem me ajuda a colocar um count nessas faixas etarias

SELECT
case when trunc((current_date - ricadpac.nasc) / 365.25) <= 14 then
'0 a 14'
when trunc((current_date - ricadpac.nasc) / 365.25) between 15 and 28 then
'15 a 28'
when trunc((current_date - ricadpac.nasc) / 365.25) between 29 and 42 then
'29 a 42'
when trunc((current_date - ricadpac.nasc) / 365.25) between 43 and 56 then
'43 a 56'
when trunc((current_date - ricadpac.nasc) / 365.25) between 57 and 71 then
'57 a 71'
when trunc((current_date - ricadpac.nasc) / 365.25) between 72 and 86 then
'72 a 86'
when trunc((current_date - ricadpac.nasc) / 365.25) >= 87 then
'>=87'
end as faixa_idade),
ricadpac.sexo,
count(ricadpac.sexo) as qtd_sexo
FROM RECADATE
INNER JOIN ATCABECATEND ON atcabecatend.id = RECADATE.ID_ATCABECATEND
INNER JOIN ricadpac ON RICADPAC.ID = atcabecatend.id_ricadpac
LEFT JOIN ATCABECATEND_TBSENHAATEND ON atcabecatend_tbsenhaatend.ID_ATCABECATEND = ATcabecatend.ID
LEFT JOIN TBFLUXOATEND ON tbfluxoatend.ID_TBSENHAATEND = atcabecatend_tbsenhaatend.ID_TBSENHAATEND
AND tbfluxoatend.ACAO='C'
INNER JOIN tblocal ON TBLOCAL.ID=TBFLUXOATEND.id_tblocal
inner join tbunidad u on u.cod=recadate.unidade
WHERE RECADATE.UNIDADE IN (2,12,14,15,17,24,25,41)
and TBFLUXOATEND.ID_TBLOCAL IN (7,21,27)
AND CAST(ATCABECATEND.DATA_HORA_ENTRADA AS DATE) between :data1 and :data2
group by u.nome, ricadpac.nasc, ricadpac.sexo
ORDER BY u.nome

Marcos Batista

Curtir t�pico + 0

Responder

Posts

19/04/2022

Emerson Nascimento

SELECT
	u.nome,
	ricadpac.sexo,
	count(ricadpac.sexo) as qtd_sexo,
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) <= 14 then u.nome else null end) '0 a 14',
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 15 and 28 then u.nome else null end) '15 a 28',
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 29 and 42 then u.nome else null end) '29 a 42',
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 43 and 56 then u.nome else null end) '43 a 56',
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 57 and 71 then u.nome else null end) '57 a 71',
	count(case when trunc((current_date - ricadpac.nasc) / 365.25) >= 87 then u.nome else null end) '>=87'
FROM
	RECADATE
INNER JOIN
	ATCABECATEND ON atcabecatend.id = RECADATE.ID_ATCABECATEND
INNER JOIN
	ricadpac ON RICADPAC.ID = atcabecatend.id_ricadpac
LEFT JOIN
	ATCABECATEND_TBSENHAATEND ON atcabecatend_tbsenhaatend.ID_ATCABECATEND = ATcabecatend.ID
LEFT JOIN
	TBFLUXOATEND ON tbfluxoatend.ID_TBSENHAATEND = atcabecatend_tbsenhaatend.ID_TBSENHAATEND
	AND tbfluxoatend.ACAO='C'
INNER JOIN
	tblocal ON TBLOCAL.ID=TBFLUXOATEND.id_tblocal
inner join
	tbunidad u on u.cod=recadate.unidade
WHERE
	RECADATE.UNIDADE IN (2,12,14,15,17,24,25,41)
	AND TBFLUXOATEND.ID_TBLOCAL IN (7,21,27)
	AND CAST(ATCABECATEND.DATA_HORA_ENTRADA AS DATE) between :data1 and :data2
group by
	u.nome, ricadpac.sexo
ORDER BY
	u.nome

Responder

Gostei + 0