SQL : Query Show date range with if

Question

I have a problem about syntax in MySQL... let me show to you

Expected Result

| ID | Date            | Status |
|-----|----------------|----------|
| 2 | 2014-06-10 | H         |
| 2 | 2014-06-11 | O         |
| 2 | 2014-06-12 | H         |
| 2 | 2014-06-13 | -          |
| 2 | 2014-06-14 | H         |

Problem
in my database Date = 2014-06-13 not found but I want still show with status ' - ' .

Query have tried

SELECT id,date,status 
FROM v_absensi 
WHERE site_absensi='IDJK068PBP' 
  AND tanggal != 'Belum Ada' 
  AND DATE_FORMAT( STR_TO_DATE( tanggal ,'%d/%m/%Y' ) ,'%Y/%m/%d' )
      BETWEEN DATE_FORMAT( STR_TO_DATE( '18/06/2014' ,'%d/%m/%Y' ) ,'%Y/%m/%d' )
      AND DATE_FORMAT( STR_TO_DATE( '23/06/2014' ,'%d/%m/%Y' ) ,'%Y/%m/%d' ) 
  AND id='2'

Need your Advice Guys
Thanks for your advice !!!

Already asked several times: [this](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) or [this](http://stackoverflow.com/questions/13754598/mysql-show-all-dates-between-even-if-no-result) may help you. — Nidheesh, Jun 23 '14 at 03:49

Nidheesh · Accepted Answer · 2014-06-23T10:05:44.563

Create a dummy table like this.

CREATE TABLE aDummyTable ( id int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (id) );

Then insert some values:

INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);

Consider this as your table:

CREATE TABLE  urTable (
  id int(10) unsigned NOT NULL auto_increment,
  dateVal date,
  val varchar(10),
   PRIMARY KEY  (id)
);

Insert some values for example :

INSERT INTO urTable  (id,dateVal,val)VALUES   (1,'2014-06-10','H');
INSERT INTO urTable  (id,dateVal,val)VALUES   (2,'2014-06-11','O');
INSERT INTO urTable  (id,dateVal,val)VALUES   (3,'2014-06-12','H');
INSERT INTO urTable  (id,dateVal,val)VALUES   (4,'2014-06-14','H');

Here is your result.

SELECT x.ts AS time,
         COALESCE(y.val, '-') AS urVal
     FROM (SELECT DATE_FORMAT(DATE_ADD('2014-06-10', INTERVAL n.id - 1 DAY), '%Y-%m-%d') AS ts
             FROM aDummyTable n
            WHERE DATE_ADD('2014-06-10', INTERVAL n.id - 1 DAY) <= '2014-06-14') x
LEFT JOIN urTable y  ON  DATE_FORMAT(dateVal, '%Y-%m-%d') = x.ts;

Fiddle

sorry for the late response... thants work guys.. thanks alot — user3765962, Jun 27 '14 at 03:03

SQL : Query Show date range with if

1 Answers1