Minerva :: MySQL Date Sequence

Read 178 times | Created 2013-06-27 09:46:54 | Updated 2017-09-21 08:39:45 | | |
DROP TABLE IF EXISTS `a`;
CREATE TABLE IF NOT EXISTS `a` (
  `nama` varchar(11) NOT NULL,
  `tgl1` date NOT NULL,
  `tgl2` date NOT NULL
) ENGINE=MyISAM;
 
 
INSERT INTO `a` (`nama`, `tgl1`, `tgl2`) VALUES
('bejo', '2013-06-29', '2013-07-03'),
('surti', '2013-07-01', '2013-07-05');

SELECT * FROM a;
+-------+------------+------------+
| nama  | tgl1       | tgl2       |
+-------+------------+------------+
| bejo  | 2013-06-29 | 2013-07-03 |
| surti | 2013-07-01 | 2013-07-05 |
+-------+------------+------------+

SELECT 
  a.nama,
  DATE_ADD(a1.t1,INTERVAL td.digit DAY) AS tgl
FROM
  (
    SELECT (b1.d+b2.d+b4.d+b8.d+b16.d) AS digit
    FROM 
      (SELECT 0 d UNION ALL SELECT 1 d ) b1
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 2 d ) b2
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 4 d ) b4
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 8 d ) b8
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 16 d ) b16
  ) td,
  (
    SELECT MIN( tgl1 ) t1, MAX( tgl2 ) t2
    FROM a
  ) a1,
  a
WHERE 
  a.tgl1<=DATE_ADD(a1.t1,INTERVAL td.digit DAY) 
  AND a.tgl2>=DATE_ADD(a1.t1,INTERVAL td.digit DAY);
  
+-------+------------+
| nama  | tgl        |
+-------+------------+
| bejo  | 2013-06-29 |
| bejo  | 2013-06-30 |
| bejo  | 2013-07-01 |
| bejo  | 2013-07-02 |
| bejo  | 2013-07-03 |
| surti | 2013-07-01 |
| surti | 2013-07-02 |
| surti | 2013-07-03 |
| surti | 2013-07-04 |
| surti | 2013-07-05 |
+-------+------------+

SELECT 
  a.nama,
  COUNT(*) AS jml
FROM
  (
    SELECT (b1.d+b2.d+b4.d+b8.d+b16.d) AS digit
    FROM 
      (SELECT 0 d UNION ALL SELECT 1 d ) b1
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 2 d ) b2
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 4 d ) b4
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 8 d ) b8
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 16 d ) b16
  ) td,
  (
    SELECT MIN( tgl1 ) t1, MAX( tgl2 ) t2
    FROM a
  ) a1,
  a
WHERE 
  a.tgl1<=DATE_ADD(a1.t1,INTERVAL td.digit DAY) 
  AND a.tgl2>=DATE_ADD(a1.t1,INTERVAL td.digit DAY)
GROUP BY a.nama;

+-------+-----+
| nama  | jml |
+-------+-----+
| bejo  |   5 |
| surti |   5 |
+-------+-----+

SELECT 
  DATE_ADD(a1.t1,INTERVAL td.digit DAY) AS tgl,
  COUNT(*) AS jml
FROM
  (
    SELECT (b1.d+b2.d+b4.d+b8.d+b16.d) AS digit
    FROM 
      (SELECT 0 d UNION ALL SELECT 1 d ) b1
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 2 d ) b2
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 4 d ) b4
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 8 d ) b8
      CROSS JOIN (SELECT 0 d UNION ALL SELECT 16 d ) b16
  ) td,
  (
    SELECT MIN( tgl1 ) t1, MAX( tgl2 ) t2
    FROM a
  ) a1,
  a
WHERE 
  a.tgl1<=DATE_ADD(a1.t1,INTERVAL td.digit DAY) 
  AND a.tgl2>=DATE_ADD(a1.t1,INTERVAL td.digit DAY)
GROUP BY tgl
ORDER BY tgl;

+------------+-----+
| tgl        | jml |
+------------+-----+
| 2013-06-29 |   1 |
| 2013-06-30 |   1 |
| 2013-07-01 |   2 |
| 2013-07-02 |   2 |
| 2013-07-03 |   2 |
| 2013-07-04 |   1 |
| 2013-07-05 |   1 |
+------------+-----+
Snippet Code MySQL Date Sequence
