get records of last month from current year & last-CodePudding

I'm very new to mysql so excuse me, I need to get records of the last month of the soldAt column, so for e.g. there's a soldAt date yyyy-mm-dd 2022-05-12 I get the record for its whole previous month. I'm using this query

SELECT at.* 
from Analytics as at
where MONTH(at.SoldAtUpdatedAt) = MONTH(NOW() - INTERVAL 1 MONTH)
 and YEAR(at.SoldAtUpdatedAt)= YEAR(NOW())

This query works fine and i get previous month results for current year but what if my soldAtUpdatedAt has a date of 2022-01-01 i.e. 1st of january of 2022 i want to get the december month record for year 2021. How do i query this part? I read about Datediff but i get 0 rows when i fetch.. Any guidance would be appreciated!. Also I tried (datepart didnt work in mysql) dateadd , datediff but i get zero rows...

CodePudding user response：

Optimal query is

SELECT * 
FROM Analytics
WHERE SoldAtUpdatedAt >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
  AND SoldAtUpdatedAt < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

DATE_FORMAT(date_value_or_expression, '%Y-%m-01') creates the date for 1st day of date's month (with zero timepart). This expression is constant, hence it is calculated once - and not while execution but as a step of the execution plan building.

This query is SARGable - the query may use the index by SoldAtUpdatedAt.

CodePudding user response：

This WHERE expression gets you the current month.

WHERE SoldAtUpdatedAt >= LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 1 MONTH
  AND SoldAtUpdatedAt <  LAST_DAY(NOW())   INTERVAL 1 DAY

This one gets you the same month a year ago.

WHERE SoldAtUpdatedAt >= LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 13 MONTH
  AND SoldAtUpdatedAt <  LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 12 MONTH

So, something like this should get you a summary of your table for those two months.

SELECT LAST_DAY(SoldAtUpdatedAt) month_ending, SUM(sales) sales, COUNT(*) numb
  FROM Analytics 
 WHERE (     SoldAtUpdatedAt >= LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 1 MONTH
         AND SoldAtUpdatedAt <  LAST_DAY(NOW())   INTERVAL 1 DAY)
    OR (     SoldAtUpdatedAt >= LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 13 MONTH
         AND SoldAtUpdatedAt <  LAST_DAY(NOW())   INTERVAL 1 DAY - INTERVAL 12 MONTH)
 GROUP BY LAST_DAY(SoldAtUpdatedAt)
 ORDER BY LAST_DAY(SoldAtUpdatedAt);

LAST_DAY() comes in handy here both for month computations and grouping. It happens to be portable outside MySQL.

DATE_FORMAT(NOW(), '%Y-%m-01') also works to get the first day of the present month.