Jmsilva Posted July 1, 2023 Report Share Posted July 1, 2023 Considerando a tabela 'process' com campos id,dtcalc1 e dtcalc2. Pesquisa feita ente 01/06/2023 e 07/06/2023 id - dtcalc1 - dtcalc2 resultado em dias 1 03/06/2023 05/06/2023 2 dias 2 04/06/2023 05/06/2023 1 dia 3 04/06/2023 06/06/2023 2 dia 4 06/06/2023 07/06/2023 1 dia 5 06/06/2023 06/06/2023 0 dia excessão * Excessão : como as datas são iguais seria 0 dia, mas deve contar como um quando for iguais * para os demais caso, o dia deve estar entre ( dtcalc1 >= e dtcalc2 < ) Resultado esperado 01/06 = 0 02/06 = 0 03/06 = 1 04/06 = 3 05/06 = 1 06/06 = 2 07/06 = 0 Atualmente faço For..Next com Select count(*) , porem se for 30 dias, são 30 requisições...queria mudar para uma única. att. JMSILVA Quote Link to comment Share on other sites More sharing options...
oliveiros Posted July 3, 2023 Report Share Posted July 3, 2023 Oi, bom dia! Explica melhor a demanda, por favor. Vc precisa colocar a quantidade de dias entre as datas dtcalc1 e dtcalc2, isso? SELECT p.dtcalc1 AS data, SUM( IF( TIMESTAMPDIFF(DAY, p.dtcalc1, p.dtcalc2) = 0, 1, TIMESTAMPDIFF(DAY, p.dtcalc1, p.dtcalc2) ) ) AS dias FROM process AS p GROUP BY p.dtcalc1; Testa essa Query. O resultado será: 03/06/2023 - 2; 04/06/2023 - 3; 06/06/2023 - 2; Não entendi na sua demanda dia 05/06/2023 - 1; Atenciosamente, Oliveiros Junior Quote Link to comment Share on other sites More sharing options...
Jmsilva Posted July 4, 2023 Author Report Share Posted July 4, 2023 19 horas atrás, oliveiros disse: Oi, bom dia! Explica melhor a demanda, por favor. Vc precisa colocar a quantidade de dias entre as datas dtcalc1 e dtcalc2, isso? SELECT p.dtcalc1 AS data, SUM( IF( TIMESTAMPDIFF(DAY, p.dtcalc1, p.dtcalc2) = 0, 1, TIMESTAMPDIFF(DAY, p.dtcalc1, p.dtcalc2) ) ) AS dias FROM process AS p GROUP BY p.dtcalc1; Testa essa Query. O resultado será: 03/06/2023 - 2; 04/06/2023 - 3; 06/06/2023 - 2; Não entendi na sua demanda dia 05/06/2023 - 1; Atenciosamente, Oliveiros Junior Olá, obrigado vou testar. Mas com relação aos resultados são: Dia 03 = 1 ref. Id 1 Dia 04 = 3 ref. Id 1,2 e 3 Dia 05 = 1 ref Id 3 Dia 06 = 2 ref id 4 e 5 Dia 07 = 0 Espero ter respondido. Quote Link to comment Share on other sites More sharing options...
oliveiros Posted July 4, 2023 Report Share Posted July 4, 2023 Olá, agora entendi. select a.Date, ( SELECT COUNT(*) from process AS p WHERE a.Date >= p.dtcalc1 and If( TIMESTAMPDIFF(DAY, p.dtcalc1, p.dtcalc2) = 0 , a.Date <= p.dtcalc2, a.Date < p.dtcalc2 ) GROUP BY a.Date) AS ocorrencia from ( select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) a where a.Date between '2023-06-01' and '2023-06-07' GROUP BY a.Date ORDER BY a.Date Essa query vai retorna algo assim 2023-06-01 - NULL 2023-06-02 - NULL 2023-06-03 - 1 2023-06-04 - 3 2023-06-05 - 1 2023-06-06 - 2 2023-06-07 - NULL Att., Oliveiros Junior Jmsilva 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.