· 9 years ago · Jan 23, 2017, 10:42 AM
1DROP TABLE IF EXISTS `cuti`;
2CREATE TABLE IF NOT EXISTS `cuti` (
3 `nama` varchar(11) NOT NULL,
4 `tgl1` date NOT NULL,
5 `tgl2` date NOT NULL
6) ENGINE=MyISAM;
7
8
9INSERT INTO `cuti` (`nama`, `tgl1`, `tgl2`) VALUES
10('bejo', '2016-06-29', '2016-07-03'),
11('agus', '2016-07-03', '2016-07-07'),
12('surti', '2016-07-01', '2016-07-05');
13
14SELECT
15 COUNT(a.nama) AS jml,
16 DATE_ADD(a1.t1,INTERVAL td.digit DAY) AS tgl
17FROM
18 (
19 SELECT (b1.d+b2.d+b4.d+b8.d+b16.d) AS digit
20 FROM
21 (SELECT 0 d UNION ALL SELECT 1 d ) b1
22 CROSS JOIN (SELECT 0 d UNION ALL SELECT 2 d ) b2
23 CROSS JOIN (SELECT 0 d UNION ALL SELECT 4 d ) b4
24 CROSS JOIN (SELECT 0 d UNION ALL SELECT 8 d ) b8
25 CROSS JOIN (SELECT 0 d UNION ALL SELECT 16 d ) b16
26 ) td,
27 (
28 SELECT MIN( tgl1 ) t1, MAX( tgl2 ) t2
29 FROM cuti
30 ) a1,
31 cuti a
32WHERE
33 a.tgl1<=DATE_ADD(a1.t1,INTERVAL td.digit DAY)
34 AND a.tgl2>=DATE_ADD(a1.t1,INTERVAL td.digit DAY)
35GROUP BY
36 DATE_ADD(a1.t1,INTERVAL td.digit DAY) ;
37
38+-----+------------+
39| jml | tgl |
40+-----+------------+
41| 1 | 2016-06-29 |
42| 1 | 2016-06-30 |
43| 2 | 2016-07-01 |
44| 2 | 2016-07-02 |
45| 3 | 2016-07-03 |
46| 2 | 2016-07-04 |
47| 2 | 2016-07-05 |
48| 1 | 2016-07-06 |
49| 1 | 2016-07-07 |
50+-----+------------+