0

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?

0

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);
  • MariaDB's "seq_n_m" pseudo tables make this so much simpler. – Rick James Aug 6 '16 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.