I am trying to write a query to return Month-To-Date data for the Current Year, and also the Previous Year.
Meaning I need data from 2017-07-01 to 2017-07-26 and also 2016-07-01 to 2016-07-26.
However, my query currently returns data from 2017-07-01 to 2017-07-26 (correctly) but also returns data from 2016-07-01 to 2016-07-31 (incorrectly).
How can I have the data for the same time period (MTD) from two different years?
The table includes date, day in quarter, year, quarter, month and week but no day column...
SELECT
data_up_to,
date,
year,
month,
sum(sales)
FROM
salesdata.sales
WHERE
MONTH = MONTH(NOW()) and
YEAR in (YEAR(NOW()), YEAR(NOW())-1, YEAR(NOW())-2) and
date <= data_up_to
I would go like this:
SELECT year
, month
, sum(sales)
from salesdata.sales
--Selecting were day in range with same month
WHERE DAY(date) BETWEEN 1 AND DAY(data_up_to)
AND MONTH = MONTH(NOW())
AND YEAR IN (YEAR(NOW()), YEAR(NOW())-1)
--Group by Year, Month
GROUP BY YEAR
, MONTH
When you want to refer to MTD of NOW() You can use:
SELECT year
, month
, sum(sales)
from salesdata.sales
--Selecting were day in range with same month
WHERE DAY(date) BETWEEN 1 AND DAY(NOW())
AND MONTH = MONTH(NOW())
AND YEAR IN (YEAR(NOW()), YEAR(NOW())-1)
--Group by Year, Month
GROUP BY YEAR
, MONTH
I deleteted date from the query in order enable the grouping by month and year.