Read 339 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 | +------------+-----+