Get Data from datewise

Question

I have some records in database. Now I want to get the record from date wise. Please see the below format of my table.

 User_id    name    date         Present
  001      Jone    01-08-2012     yes
  002      arun    01-08-2012     yes
  001      Jone    02-08-2012     yes
  002      arun    02-08-2012     yes
  001      Jone    03-08-2012     yes
  001      Jone    04-08-2012     yes
  002      arun    05-08-2012     yes
  001      Jone    05-08-2012     yes
  001      Jone    06-08-2012     yes
  001      arun    06-08-2012     yes

I want get "arun" report from 01-08-2012 to 07-08-2012 like this

 User_id    name    date         Present
  002      arun    01-08-2012     yes
  002      arun    02-08-2012     yes
  002      arun    03-08-2012    
  002      arun    04-08-2012     
  002      arun    06-08-2012     yes
  002      arun    07-08-2012     

Answer 1

In order to display the missing date, you would need a temp table. temp table:

CREATE TABLE numbers (number INTEGER);
INSERT INTO numbers VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

sql as below:

SELECT CASE WHEN u.id IS NOT NULL 
       THEN u.id
       ELSE u2.id
END AS id ,
CASE WHEN u.name IS NOT NULL 
       THEN u.name
       ELSE u2.name
END AS name ,
cal.date, 
CASE WHEN u.present IS NOT NULL 
       THEN u.present
       ELSE ''
END AS present 
FROM (
    SELECT '2012-08-01' + INTERVAL a.number * 10 + b.number DAY as date
    FROM numbers a JOIN numbers b
    ORDER BY a.number * 10 + b.number
) cal LEFT JOIN (select * from users where name='arun') u 
ON DATE_FORMAT(STR_TO_DATE(u.date1, '%d-%m-%Y'), '%Y-%m-%d') = cal.date
left join (select id,name from users  group by id)u2 on u.id is null and u2.name='arun'
WHERE date BETWEEN '2012-08-01' AND '2012-08-07';

SQL DEMO HERE.