Dynamic , Using a Case Statement in a Where Clause in mysql

user_17828477 0
Viewed 99

I am stucked at a dynamic where clause inside case statement.

WHAT I NEED

When i used this

SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date LIKE 
CASE WHEN f.Date  BETWEEN (last_day(curdate() - interval 1 month) + interval 1 day) AND last_day(curdate())  
                  THEN f.Date  ELSE f.date between subdate(curdate(),interval 1 month) and (last_day(curdate() - interval 1 month)) END
ORDER BY f.id desc;

The syntax is wrong but when instead it '2022-04%'.

SELECT col1,col2,col3

FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date LIKE 
CASE WHEN f.Date  BETWEEN (last_day(curdate() - interval 1 month) + interval 1 day) AND last_day(curdate())  
                  THEN f.Date  ELSE '2022-04%' END
ORDER BY f.id desc;

It is correct but i want to change dynamically.how can i do it. I mean that when i run the query include date of 1-4-2022 to 30-4-2022. The snapshot my database include data of yesterday in the begin of month i have the issue.

mysql-connector mysql-workbench mysql
1 Answers

A case statement can only return a value and not a range. If I understand rightly it is only the start date which changes so we only need to decide the start date in the case statement. If the end date also changes we will need a second case statement. However it looks like a simple test will return the same results

SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date BETWEEN
curdate() - interval 1 month
  AND
last_day(curdate())
ORDER BY f.id desc;

Re-reading your explanation I think that you really want

SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE month(f.date) = month(curdate() - interval 1 day)
AND year(f.date) = year(curdate() - interval 1 day )
ORDER BY f.id desc;

db<>fiddle here

Admin1
Related