CODEWITHSUNDEEP
mysql
Blake
Blake

Reputation: 43

Select previous 12 months by month and year input

I am stuck trying to return the totals for the last 12 months when month and year are used as inputs.

I can get a whole year (from January to December) based on an input, but what I am after is say if i input ('08','2017') it will calculate totals for 08/2016-08/2017.

I know i will have to add in another input and assign it a value e.g IN targetMonth int, but I am unsure of how to go about the INTERVAL.

The 'date_admitted' column has values in the form of '2010-01-30'. Essentially the resulting table will look like:

Month    Total  %
08 2016   10    16
09 2016   10    16
10 2016   10    16
11 2016   10    16
12 2016   10    16
01 2017   10    16
  etc    etc   etc

heres my code for calcuating a whole year

DROP PROCEDURE IF EXISTS YrReport;
DELIMITER //
CREATE PROCEDURE YrReport( 
IN targetYear int)
BEGIN
SELECT DATE_FORMAT(date_admitted, '%m') 
AS Month,
COUNT(id) 
AS Total,
ROUND(COUNT(id) / (SELECT COUNT(id) FROM 
accessions  
WHERE year(date_admitted) = targetYear)*100)
AS '%'
FROM accessions 
WHERE year(date_admitted) = targetYear
GROUP BY DATE_FORMAT(date_admitted, '%Y%m');
END //
DELIMITER ;

Upvotes: 1

Views: 643

Answers (2)

Binarus
Binarus

Reputation: 4405

I didn't look into the whole code you wrote, but it seems that you can solve your problem by changing your WHERE conditions. Let us suppose you have a second parameter to your stored procedure,

....
IN targetMonth int)

then you could replace

WHERE year(date_admitted) = targetYear

by

WHERE ((YEAR(date_admitted) = targetYear) AND
       (MONTH(date_admitted) < targetMonth)) OR
      ((YEAR(date_admitted) = (targetYear - 1)) AND
       (MONTH(date_admitted) >= targetMonth))

and change the other WHERE condition in a similar way if necessary.

Coming back to your example, this would select 08/2016 (included) to 07/2017 (included) which is 12 months. If you want it to select 09/2016 to 08/2017 instead, you can adapt the code.

I think this will run quite fast compared to other solutions, but it will work only if you want to include exactly one year. You cannot easily adapt that code to include 13 months, for example.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108706

Here's what you need:

If you have a numeric year, for example 2016, you can use an expression like this to retrieve all rows with date_admitted in that calendaryear.

WHERE date_admitted >= MAKEDATE(year, 1)
  AND date_admitted <  MAKEDATE(year, 1) + INTERVAL 1 YEAR

If you have some datestamp for a day, and you want to retrieve the twelve full months of data preceding that day (not year-to-date), do this.

WHERE date_admitted >= LAST_DAY(datestamp) + INTERVAL 1 DAY - INTERVAL 13 MONTH
  AND date_admitted <  LAST_DAY(datestamp) + INTERVAL 1 DAY - INTERVAL 1 MONTH

This can be especially useful for the current date.

WHERE date_admitted >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 13 MONTH
  AND date_admitted <  LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH

If you need to group by month do something like this

 SELECT SUM(something), COUNT(*), LAST_DAY(date_admitted) month_ending
   FROM someTable
  GROUP BY LAST_DAY(date_admitted)

The use of MySQL date arithmetic can get a little verbose. But it allows you to do lots of date filtering and grouping with precision.

One idiom is this. The first day of the month containing a datestamp is

LAST_DAY(datestamp) + INTERVAL 1 DAY - INTERVAL 1 MONTH

Upvotes: 0

Related Questions