是否可以在单个查询中选择MonthToDate，LastYearMonthToDate，YearToDate和LastYearToDate？

Question

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!

Answer 1

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;