Mysql Interval with IN() function returning only one row

Question

It returning only one row of existing data of that month, required all interval 6 month

SELECT YEAR(m.dte) as vacyear, 
       MONTHNAME(m.dte + INTERVAL 1 month) as vacmonth, 
       MONTH(m.dte + INTERVAL 1 month) as vmonth,  
       COUNT(v.created_date) as total
FROM (SELECT DATE_ADD(NOW(), INTERVAL -12 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -11 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -10 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -9 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -8 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -7 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -6 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -5 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -4 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -3 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -2 MONTH) as dte UNION ALL
      SELECT DATE_ADD(NOW(), INTERVAL -1 MONTH) as dte
      ) as m 
LEFT JOIN tbl_team v ON v.created_date >= m.dte 
                    AND v.created_date < m.dte + interval 1 month 
WHERE m.dte >= DATE_ADD(NOW(), INTERVAL - 6 MONTH) 
  AND v.id IN(7,8,9)
GROUP BY vacmonth, vmonth
ORDER BY vmonth ASC

Provide some sample data (as CREATE TABLE + INSERT INTO) and desired output for it. PS. `vacyear` must be added to GROUP BY expression. — Akina, Nov 17 '20 at 05:18
`AND v.id IN(7,8,9)` converts your LEFT JOIN to INNER JOIN. Move this condition to ON clause. — Akina, Nov 17 '20 at 05:20
don't required to convert to inner join only required ON claudse — Dattatray Patap, Nov 17 '20 at 05:29
Condition by right table in WHERE always converts LEFT to INNER implicitly. — Akina, Nov 17 '20 at 05:30
Just to get the facts right: IN is an operator, not a function. — Shadow, Nov 17 '20 at 07:38

Mysql Interval with IN() function returning only one row

0 Answers0