- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
builder.append(" SELECT ");
for(int i=7;i>0;i--){
int month = cal.get(Calendar.MONTH) + 1;
int year = cal.get(Calendar.YEAR);
cal.add(Calendar.MONTH, -1);
builder.append(" IFNULL(SUM(IF((MONTH(FEED.feed_date)= "+ month +" && YEAR(FEED.feed_date)="+year+"),"+measurementByColumn+",0)),0) AS M"+i+ " , ");
}
builder.append(" IFNULL(MAX(IF((YEAR(FEED.feed_date)="+currentyear+" and FEED.feed_date=FEEDYEARDATA.curYearMaxFeedDate),"+measurementByColumn+",null)),0) AS CUR_YEAR_DATA, ");
builder.append(" IFNULL(MAX(IF((YEAR(FEED.feed_date)="+lastyear+" and FEED.feed_date=FEEDYEARDATA.lastYearMaxFeedDate ),"+measurementByColumn+",null)),0) AS LAST_YEAR_DATA , ");
builder.append(" FEED.pa_code,FEED.sub_pa_code ");
builder.append(" FROM lm_dealer_info_data_feed FEED ");
builder.append(" inner join ( SELECT pa_code,sub_pa_code, ");
builder.append(" MAX(IF(YEAR(feed_date)="+currentyear+",feed_date,NULL)) as curYearMaxFeedDate, ");
builder.append(" MAX(IF(YEAR(feed_date)="+lastyear+",feed_date,NULL)) as lastYearMaxFeedDate " );
builder.append(" FROM lm_dealer_info_data_feed " );
builder.append(" GROUP BY pa_code,sub_pa_code ) as FEEDYEARDATA " );
builder.append(" ON FEED.pa_code =FEEDYEARDATA.pa_code and FEED.sub_pa_code = FEEDYEARDATA.sub_pa_code " );
builder.append(" GROUP BY FEED.pa_code,FEED.sub_pa_code ");
return builder.toString();