I'm trying to select the following data in a single query: I have a date variable in standard mySQL format, which allows mySQL select statements such as YEAR(date) = '2011' and MONTH(date) = '11' to select all data from November 2011.
I'm trying to SUM a variable called sales for four distinct timeframes: Month to Date, Last Year Month to Date, Year to Date, and Last Year to Date.
Please assume I have these three variables defined before hand. $currentYear, $lastYear, and $currentMonth.
I would like to try and reduce load on database and see if it's possible to select this in a single query.
Thanks in advance!
You could use the SUM combined with CASE trick. It has got me out of many an analytical hole in the past!
Below is an example with a query at the bottom. I am not 100% sure what should be in 'Last Year Month to Date' and 'Last Year to Date' but hopefully you will get the principle of what I am driving at. In the query you'll need to replace 2012, 2011 and 1 with your $currentYear, $lastYear and $currentMonth variables respectively.
create table date_timeframe_example
(myDate datetime not null,
sales int not null default 0
);
insert into date_timeframe_example values (now() - interval 1 year + interval 3 day,51);
insert into date_timeframe_example values (now() - interval 1 year + interval 4 day,5);
insert into date_timeframe_example values (now() - interval 1 year + interval 5 day,7);
insert into date_timeframe_example values (now() - interval 1 year + interval 6 day,87);
insert into date_timeframe_example values (now() - interval 1 year + interval 7 day,12);
insert into date_timeframe_example values (now() - interval 1 year + interval 12 day,0);
insert into date_timeframe_example values (now() - interval 1 year - interval 4 day,1);
insert into date_timeframe_example values (now() - interval 1 year - interval 5 day,1235);
insert into date_timeframe_example values (now() - interval 1 year - interval 12 day,76);
insert into date_timeframe_example values (now() - interval 1 year - interval 6 day,98);
insert into date_timeframe_example values (now() - interval 1 year - interval 2 day,4);
insert into date_timeframe_example values (now() - interval 3 day,8);
insert into date_timeframe_example values (now() - interval 4 day,21);
insert into date_timeframe_example values (now() - interval 5 day,67);
insert into date_timeframe_example values (now() - interval 6 day,73);
insert into date_timeframe_example values (now() - interval 7 day,67);
insert into date_timeframe_example values (now() - interval 12 day,4);
insert into date_timeframe_example values (now() - interval 4 day,9);
insert into date_timeframe_example values (now() - interval 5 week,124);
insert into date_timeframe_example values (now() - interval 12 week,42);
insert into date_timeframe_example values (now() - interval 6 week,9);
insert into date_timeframe_example values (now() - interval 2 week,7);
select
sum(case when year(myDate) = 2012 and month(myDate) = 1 then sales else 0 end) as month_to_date,
sum(case when year(myDate) = 2011 and month(myDate) = 1 then sales else 0 end) as last_year_month_to_date,
sum(case when year(myDate) = 2012 then sales else 0 end) as year_to_date,
sum(case when year(myDate) = 2011 then sales else 0 end) as last_year_to_date
from date_timeframe_example;