Repetição de linhas/datas no sql
Bom dia/Boa tarde/Boa noite, pessoal. Estou com um problema em um comando do sql, onde preciso organizar em colunas: data, faixa etária e total, mas estou com um problema onde deveria unir todas as linhas a partir da data mas o distinct não está ajudando 100% e algumas linhas ainda estão aparecendo quando há duas faixas etárias distintas, aparece uma em cada linha. exemplo dias 30/01 tem x de 11 a 22 anos em uma linha e y de 23 a 32 anos em outra linha, sendo que deveria aparecer todos na mesma linha.
Eis a query:
select
DISTINCT(Convert(date,osm_dthr,103)), "essa linha é para organizar por ordem de data."
convert(varchar(10),osm_dthr, 103)''DATA'', "essa linha é a que será mostrada no relatório final."
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 0 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=5 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''CRIANÇA1 0 A 5 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 6 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=10 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''CRIANÇA2 6 A 10 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 11 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=21 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADOLESCENTE 11 A 21 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 22 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=40 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADULTO1 22 A 40 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 41 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=60 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADULTO2 41 A 60 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 61 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''IDOSO MAIS DE 61'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 0 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''TOTAL''
from smm, osm
where (smm_osm = osm_num) and (smm_osm_serie = osm_serie ) and (osm_dthr = osm_dthr) and (osm_dthr >= ''2019-01-01 00:00:00'' and osm_dthr <= ''2019-01-31 23:59:59'')
group by Convert(date,osm_dthr,103), convert(varchar(10),osm_dthr, 103), osm_serie, osm_num
ORDER BY Convert(date,osm_dthr,103)
Eis a query:
select
DISTINCT(Convert(date,osm_dthr,103)), "essa linha é para organizar por ordem de data."
convert(varchar(10),osm_dthr, 103)''DATA'', "essa linha é a que será mostrada no relatório final."
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 0 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=5 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''CRIANÇA1 0 A 5 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 6 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=10 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''CRIANÇA2 6 A 10 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 11 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=21 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADOLESCENTE 11 A 21 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 22 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=40 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADULTO1 22 A 40 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 41 and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) <=60 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''ADULTO2 41 A 60 ANOS'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 61 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''IDOSO MAIS DE 61'',
(SELECT count(cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int))''Idade'' FROM PAC, OSM P WHERE PAC_NASC IS NOT NULL AND PAC_DULT IS NOT NULL and cast(DATEDIFF(DAY, PAC_NASC, PAC_DULT)/365.25 as int) >= 0 AND P.OSM_PAC = PAC_REG AND OSM.OSM_SERIE = P.OSM_SERIE AND P.OSM_NUM = OSM.OSM_NUM) AS ''TOTAL''
from smm, osm
where (smm_osm = osm_num) and (smm_osm_serie = osm_serie ) and (osm_dthr = osm_dthr) and (osm_dthr >= ''2019-01-01 00:00:00'' and osm_dthr <= ''2019-01-31 23:59:59'')
group by Convert(date,osm_dthr,103), convert(varchar(10),osm_dthr, 103), osm_serie, osm_num
ORDER BY Convert(date,osm_dthr,103)
Isnar Filho
Curtidas 0
Melhor post
Emerson Nascimento
29/08/2019
tente algo assim:
SELECT convert(varchar(8),osm.osm_dthr,112) 'ORDEM', --"essa linha é para organizar por ordem de data." convert(varchar(10),osm.osm_dthr, 103) 'DATA', --"essa linha é a que será mostrada no relatório final." count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 0 and 5 then 1 else null end) AS 'CRIANÇA1 0 A 5 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 6 and 10 then 1 else null end) AS 'CRIANÇA2 6 A 10 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 11 and 21 then 1 else null end) AS 'ADOLESCENTE 11 A 21 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 22 and 40 then 1 else null end) AS 'ADULTO1 22 A 40 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 41 and 60 then 1 else null end) AS 'ADULTO2 41 A 60 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) >= 61 then 1 else null end) AS 'IDOSO MAIS DE 61', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) >= 0 then 1 else null end) AS 'TOTAL' FROM smm INNER JOIN osm ON osm.osm_num = smm.smm_osm and osm.osm_serie = ssm.smm_osm_serie and osm.osm_dthr BETWEEN '2019-01-01 00:00:00' and '2019-01-31 23:59:59' LEFT JOIN pac ON pac.pac_reg = osm.osm_pac and not pac.PAC_NASC is null and not pac.PAC_DULT is null GROUP BY convert(varchar(8),osm.osm_dthr,112), convert(varchar(10),osm.osm_dthr, 103) ORDER BY convert(varchar(8),osm.osm_dthr,112)
GOSTEI 1
Mais Respostas
Isnar Filho
29/08/2019
tente algo assim:
SELECT convert(varchar(8),osm.osm_dthr,112) 'ORDEM', --"essa linha é para organizar por ordem de data." convert(varchar(10),osm.osm_dthr, 103) 'DATA', --"essa linha é a que será mostrada no relatório final." count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 0 and 5 then 1 else null end) AS 'CRIANÇA1 0 A 5 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 6 and 10 then 1 else null end) AS 'CRIANÇA2 6 A 10 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 11 and 21 then 1 else null end) AS 'ADOLESCENTE 11 A 21 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 22 and 40 then 1 else null end) AS 'ADULTO1 22 A 40 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) between 41 and 60 then 1 else null end) AS 'ADULTO2 41 A 60 ANOS', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) >= 61 then 1 else null end) AS 'IDOSO MAIS DE 61', count(case when cast(datediff(day, pac.pac_nasc, pac.pac_dult)/365.25 as int) >= 0 then 1 else null end) AS 'TOTAL' FROM smm INNER JOIN osm ON osm.osm_num = smm.smm_osm and osm.osm_serie = ssm.smm_osm_serie and osm.osm_dthr BETWEEN '2019-01-01 00:00:00' and '2019-01-31 23:59:59' LEFT JOIN pac ON pac.pac_reg = osm.osm_pac and not pac.PAC_NASC is null and not pac.PAC_DULT is null GROUP BY convert(varchar(8),osm.osm_dthr,112), convert(varchar(10),osm.osm_dthr, 103) ORDER BY convert(varchar(8),osm.osm_dthr,112)
Caramba, e eu batendo cabeça, sabia que tirando do group by funcionaria mas não estava sabendo como... usar essa estrutura facilitou muito e funcionou perfeitamente. Agora vou analisar tudo direitinho para ver se está contando tudo direitinho e aprender o que vc fez. Obrigado, Emerson.
GOSTEI 0