SQL Month YTD and prior YTD data

Question

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

flxplzk · Accepted Answer · 2017-08-01T07:10:06.150

0

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.

edited Aug 01 '17 at 07:10

answered Jul 31 '17 at 21:20

flxplzk

127
1
1
12

thank you very much! I had to use the DAY(NOW()-1) because it was returning more data. i.e. today (8/1) I was getting data up to 8/2 with 0 values. SELECT year , month , sum(sales) from salesdata.sales --Selecting were day in range with same month WHERE DAY(date) BETWEEN 1 AND DAY(NOW()-1) AND MONTH = MONTH(NOW()) AND YEAR IN (YEAR(NOW()), YEAR(NOW())-1) --Group by Year, Month GROUP BY YEAR , MONTH – George Aug 02 '17 at 00:31
one thing to add: In order to simplify the search for correct answers on stackoverflow: If the answer was correct mark it as correct. That helps others with a similar problem finding the answers faster. regards – flxplzk Aug 03 '17 at 08:42

SQL Month YTD and prior YTD data

1 Answers1