Identificando intervalos entre datas no MySQL

Seguindo uma dúvida postada no fórum iMasters, que é a respeito de como identificar os intervalos de meses em que não há registro para um determinado usuário (o que chamamos de GAPS), irei exemplificar como resolver este problema com uma simples abordagem.

Vamos se basear na seguinte tabela:

CREATE TABLE `metas` (
  `idmetas` int(11) NOT NULL auto_increment,
  `mesano` date NOT NULL,
  `atingiu` char(1) NOT NULL,
  `idfuncionario` int(11) NOT NULL,
  PRIMARY KEY  (`idmetas`)
)

E adicionando algumas dados, teremos:

mysql> select * from metas;
+---------+------------+---------+---------------+
| idmetas | mesano     | atingiu | idfuncionario |
+---------+------------+---------+---------------+
|       1 | 2009-01-01 | S       |             1 | 
|       2 | 2009-02-01 | S       |             1 | 
|       3 | 2009-04-01 | S       |             1 | 
|       4 | 2009-05-01 | S       |             1 | 
|       5 | 2009-08-01 | S       |             1 | 
+---------+------------+---------+---------------+
5 rows in set (0.00 sec)

Pois bem! Nosso objetivo é identificar os meses em que não há registros para o usuário.

A princípio, precisamos gerar a sequência contínua dos meses para podermos verificar os buracos,
como no MySQL não temos um CONNECT BY como no Oracle, o que nos resta é gerar linhas na mão numa
uma inline view usando UNION e CROSS JOIN.

SELECT *
  FROM (
    SELECT A.A+B.B+1
      FROM (SELECT 0 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) A,
      	   (SELECT 0 B UNION ALL
      	    SELECT 10 UNION ALL
      	    SELECT 20 UNION ALL
      	    SELECT 30 UNION ALL
      	    SELECT 40 UNION ALL
      	    SELECT 50 UNION ALL
      	    SELECT 60 UNION ALL
      	    SELECT 70 UNION ALL
      	    SELECT 80 UNION ALL
      	    SELECT 90) B
    ORDER BY 1
  ) Y;

Esta query simplesmente irá gerar números de 1 a 100, o que dividindo por 12 nos dará
bastante anos para o teste.

+-----------+
| A.A+B.B+1 |
+-----------+
|         1 | 
|         2 | 
|         3 | 
|         4 | 
|         5 | 
|         6 | 
|         7 | 
|         8 | 
|         9 | 
|        10 | 
|        11 | 
...

Em seguida, basta ter uma query que retorne a primeira e última data para delimitar nossa
busca, e fazer um LEFT JOIN de nossa primeira query que gera os dias com a tabela dos dados reais, filtrando exatamente as datas que não
são encontradas na tabela dos dados (metas).

É simples, veja:

SELECT DATE_ADD(X.INICIO, INTERVAL Y.N MONTH) DATA
  FROM (
    SELECT MIN(mesano) inicio, MAX(mesano) fim
      FROM metas
      WHERE idfuncionario = 1
  ) X
  JOIN (
    SELECT A.A+B.B+1 N
      FROM (SELECT 0 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) A,
      	   (SELECT 0 B UNION ALL
      	    SELECT 10 UNION ALL
      	    SELECT 20 UNION ALL
      	    SELECT 30 UNION ALL
      	    SELECT 40 UNION ALL
      	    SELECT 50 UNION ALL
      	    SELECT 60 UNION ALL
      	    SELECT 70 UNION ALL
      	    SELECT 80 UNION ALL
      	    SELECT 90) B
    ORDER BY 1
  ) Y
    ON DATE_ADD(X.inicio, INTERVAL Y.N MONTH) < X.FIM
  LEFT JOIN metas M
    ON M.mesano = DATE_ADD(X.inicio, INTERVAL Y.N MONTH)
  WHERE M.mesano IS NULL;

Que resultará no que esperamos, isto é:

+------------+
| DATA       |
+------------+
| 2009-03-01 | 
| 2009-06-01 | 
| 2009-07-01 | 
+------------+
3 rows in set (0.00 sec)

Simples assim!

Amigo... tem um erro nesse

Amigo... tem um erro nesse Select que vc montou. Notei que caso algum funcionário tenha preenchido todos os meses, e outro esqueça de incluir algum, ele (o Select) não retorna para esse funcionário os meses que faltam para incluir.

Tentei incluir uma clausula para filtrar o código, mas sem sucesso.

A query foi projetada para

A query foi projetada para obter dados apenas de um específico usuário, para pegar de todas da tabela, você precisa modificar para:

SELECT X.idfuncionario, DATE_ADD(X.INICIO, INTERVAL Y.N MONTH) DATA
  FROM (
    SELECT idfuncionario, MIN(mesano) inicio, MAX(mesano) fim
      FROM metas
      GROUP BY idfuncionario
  ) X
  JOIN (
    SELECT A.A+B.B+1 N
      FROM (SELECT 0 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) A,
      	   (SELECT 0 B UNION ALL
      	    SELECT 10 UNION ALL
      	    SELECT 20 UNION ALL
      	    SELECT 30 UNION ALL
      	    SELECT 40 UNION ALL
      	    SELECT 50 UNION ALL
      	    SELECT 60 UNION ALL
      	    SELECT 70 UNION ALL
      	    SELECT 80 UNION ALL
      	    SELECT 90) B
    ORDER BY 1
  ) Y
    ON DATE_ADD(X.inicio, INTERVAL Y.N MONTH) < X.FIM
  LEFT JOIN metas M
    ON M.mesano = DATE_ADD(X.inicio, INTERVAL Y.N MONTH)
    AND M.idfuncionario = X.idfuncionario
  WHERE M.mesano IS NULL;

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options