SELECT depcode, department,month_of_date,year_of_date,
SUM(day1) as day1,
SUM(day2) as day2,
SUM(day3) as day3,
SUM(day4) as day4,
SUM(day5) as day5,
SUM(day6) as day6,
SUM(day7) as day7,
SUM(day8) as day8,
SUM(day9) as day9,
SUM(day10) as day10,
SUM(day11) as day11,
SUM(day12) as day12,
SUM(day13) as day13,
SUM(day14) as day14,
SUM(day15) as day15,
SUM(day16) as day16,
SUM(day17) as day17,
SUM(day18) as day18,
SUM(day19) as day19,
SUM(day20) as day20,
SUM(day21) as day21,
SUM(day22) as day22,
SUM(day23) as day23,
SUM(day24) as day24,
SUM(day25) as day25,
SUM(day26) as day26,
SUM(day27) as day27,
SUM(day28) as day28,
SUM(day29) as day29,
SUM(day30) as day30,
SUM(day31) as day31
FROM
(
SELECT k.depcode,k.department,MONTH(vstdate) as month_of_date,YEAR(vstdate) as year_of_date,
IF(DAY(vstdate)=1,COUNT(p.vn),'') as day1,
IF(DAY(vstdate)=2,COUNT(p.vn),'') as day2,
IF(DAY(vstdate)=3,COUNT(p.vn),'') as day3,
IF(DAY(vstdate)=4,COUNT(p.vn),'') as day4,
IF(DAY(vstdate)=5,COUNT(p.vn),'') as day5,
IF(DAY(vstdate)=6,COUNT(p.vn),'') as day6,
IF(DAY(vstdate)=7,COUNT(p.vn),'') as day7,
IF(DAY(vstdate)=8,COUNT(p.vn),'') as day8,
IF(DAY(vstdate)=9,COUNT(p.vn),'') as day9,
IF(DAY(vstdate)=10,COUNT(p.vn),'') as day10,
IF(DAY(vstdate)=11,COUNT(p.vn),'') as day11,
IF(DAY(vstdate)=12,COUNT(p.vn),'') as day12,
IF(DAY(vstdate)=13,COUNT(p.vn),'') as day13,
IF(DAY(vstdate)=14,COUNT(p.vn),'') as day14,
IF(DAY(vstdate)=15,COUNT(p.vn),'') as day15,
IF(DAY(vstdate)=16,COUNT(p.vn),'') as day16,
IF(DAY(vstdate)=17,COUNT(p.vn),'') as day17,
IF(DAY(vstdate)=18,COUNT(p.vn),'') as day18,
IF(DAY(vstdate)=19,COUNT(p.vn),'') as day19,
IF(DAY(vstdate)=20,COUNT(p.vn),'') as day20,
IF(DAY(vstdate)=21,COUNT(p.vn),'') as day21,
IF(DAY(vstdate)=22,COUNT(p.vn),'') as day22,
IF(DAY(vstdate)=23,COUNT(p.vn),'') as day23,
IF(DAY(vstdate)=24,COUNT(p.vn),'') as day24,
IF(DAY(vstdate)=25,COUNT(p.vn),'') as day25,
IF(DAY(vstdate)=26,COUNT(p.vn),'') as day26,
IF(DAY(vstdate)=27,COUNT(p.vn),'') as day27,
IF(DAY(vstdate)=28,COUNT(p.vn),'') as day28,
IF(DAY(vstdate)=29,COUNT(p.vn),'') as day29,
IF(DAY(vstdate)=30,COUNT(p.vn),'') as day30,
IF(DAY(vstdate)=31,COUNT(p.vn),'') as day31
from ptdepart p INNER JOIN kskdepartment k on p.depcode=k.depcode
INNER JOIN vn_stat v on p.vn=v.vn
where vstdate BETWEEN "2016-01-01" and "2016-01-31" /*ใส่เดือน*/
and p.depcode in (SELECT depcode from kskdepartment where spclty =01 and on_desk ='Y')/* spclty 1 = อายุรกรรม , on_desk Y = เปิดใช้งาน */
GROUP BY DATE(vstdate),MONTH(vstdate),k.department
)t GROUP BY t.month_of_date,t.department
ORDER BY year_of_date,month_of_date,department