Senin, 21 Juli 2014

Query Generate Range Tanggal

Posted on 16.09 by Thinkerboy
Berikut terlampir contoh untuk menggenerate range tanggal :

SELECT 
 a.tanggal 
FROM (
 SELECT 
  CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS tanggal
 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
) a
WHERE a.tanggal BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY ) AND CURDATE()
ORDER BY a.tanggal DESC

Tinggal disesuaikan pada bagian 
WHERE a.tanggal BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH ) AND CURDATE()

Hasilnya sebagai berikut :
Posted in , , , |