CODEWITHSUNDEEP
mysqlsql
choloboy
choloboy

Reputation: 815

Dynamic SQL Version of Query in MySQL

I have the following MySQL query that captures patients given date related criteria. I want to run this query and captures results for each month in last quarter.

Is there a way to dynamically have the dates in the BETWEEN command change for each month in last quarter? I am looking for ways to optimize the SQL rather than copy paste the query to change dates and union. 

SELECT 
m.user_id,
DATE(STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d')) AS DateEXP,
IFNULL(um5.meta_value, 0) AS vID
FROM
wp_usermeta m
    INNER JOIN wp_usermeta um1 ON m.user_id = um1.user_id AND um1.meta_key = 'year'
    INNER JOIN wp_usermeta um2 ON m.user_id = um2.user_id AND um2.meta_key = 'month'
    INNER JOIN wp_usermeta um3 ON m.user_id = um3.user_id AND um3.meta_key = 'day'
    LEFT JOIN wp_usermeta um5 ON m.user_id = um5.user_id AND um5.meta_key = 'vID'
WHERE
STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value),'%Y-%m-%d') BETWEEN '2017-07-01' AND '2017-07-31'
GROUP BY m.user_id

Thank you.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I am not sure why a GROUP BY is needed for your query, given that you have no aggregation. You can do what you want by joining in the months. Something like this:

SELECT m.user_id, mons.mon_start,
       COALESCE(um5.meta_value, 0) AS vID
FROM wp_usermeta m INNER JOIN
     wp_usermeta um1
     ON m.user_id = um1.user_id AND um1.meta_key = 'year' INNER JOIN 
     wp_usermeta um2
     ON m.user_id = um2.user_id AND um2.meta_key = 'month' INNER JOIN
     wp_usermeta um3 
     ON m.user_id = um3.user_id AND um3.meta_key = 'day' LEFT JOIN
     wp_usermeta um5
     ON m.user_id = um5.user_id AND um5.meta_key = 'vID' CROSS JOIN
     (SELECT date('2017-07-01') as mon_start UNION ALL
      SELECT date('2017-08-01') as mon_start UNION ALL
      SELECT date('2017-09-01') as mon_start 
     ) mons
WHERE STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') >= mons.mon_start AND
     STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') < mons.mon_start + interval 1 month;

Upvotes: 1

Related Questions