ตัวอย่าง SQL แสดงจำนวน Leads ที่สร้างขึ้นในแต่ละเดือน Group ตาม Department

$current_year= date("Y");
$query = " SELECT  ";
for($i=1;$i<=12;$i++){

$first_of_month = gmmktime(0,0,0,sprintf('%02d',$i),1,$current_year);
$days_in_month = gmdate('t',$first_of_month);

$query .=" SUM( 
  CASE WHEN DATE( createdtime ) 
  BETWEEN DATE('".$current_year."-".sprintf('%02d',$i)."-01') 
  AND LAST_DAY('".$current_year."-".sprintf('%02d',$i)."-".$days_in_month."') 
  THEN 1 
  ELSE 0 
  END ) AS c_".$i.",";
}  
   
$query .=" SUM( 
  CASE WHEN DATE( createdtime ) 
  BETWEEN DATE('".$current_year."-01-01') 
  AND LAST_DAY('".$current_year."-12-31') 
  THEN 1 
  ELSE 0 
  END ) AS c_all,
  department,
 FROM tbl_leads
 GROUP BY department
 Having c_all>0 ";