How to pass, specific day month and year in the below query?

Question

I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in below query?

SELECT AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR('2014-02-06'),1) 
             + INTERVAL (MONTH('2014-02-06')-1) MONTH 
             + INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH('2014-02-06')
) AAA LEFT JOIN (SELECT date_field,val FROM school) BBB USING (date_field)

Can you please help me how can I pass the day month and year in the above query?

Answer 1

First create a table for as calendar in your database and fill it with few years data.

CREATE TABLE `calendar` (
`date_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then prepare your query like :

set @dy='6',@mnth='2',@yr='2016';

select c.date_field,IFNULL(t.val,0) val
from calendar c
  left outer join 
   (SELECT date_field,val FROM school) t 
    USING (date_field)
 where if(@dy='',1,day(c.date_field)=@dy)
   and if(@mnth='',1,month(c.date_field)=@mnth)
   and if(@yr='',1,year(c.date_field=@yr);