วันเสาร์ที่ 16 มกราคม พ.ศ. 2559

ตรวจสอบ User : ปริมาณข้อมูลที่บันทึก

คนสาสุข ลำปาง ใจดีจริง  ๆ มีอะไรใหม่ ๆ มาให้เรื่อย ๆ ขอบคุณครับ
SELECT concat(u.fname,' ',u.lname,'(',u.officerposition,')') as us,
COUNT(distinct visit.visitno) as total,
count(distinct case when month(visit.visitdate)=10 then  visit.visitno else null end) as m10,
count(distinct case when month(visit.visitdate)=11 then  visit.visitno else null end) as m11,
count(distinct case when month(visit.visitdate)=12 then  visit.visitno else null end) as m12,
count(distinct case when month(visit.visitdate)=1 then  visit.visitno else null end) as m1,
count(distinct case when month(visit.visitdate)=2 then  visit.visitno else null end) as m2,
count(distinct case when month(visit.visitdate)=3 then  visit.visitno else null end) as m3,
count(distinct case when month(visit.visitdate)=4 then  visit.visitno else null end) as m4,
count(distinct case when month(visit.visitdate)=5 then  visit.visitno else null end) as m5,
count(distinct case when month(visit.visitdate)=6 then  visit.visitno else null end) as m6,
count(distinct case when month(visit.visitdate)=7 then  visit.visitno else null end) as m7,
count(distinct case when month(visit.visitdate)=8 then  visit.visitno else null end) as m8,
count(distinct case when month(visit.visitdate)=9 then  visit.visitno else null end) as m9,
DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS com_lastupdate
FROM visit inner join `user` u on visit.username = u.username
WHERE visit.visitdate between '2014-10-01' and '2015-09-30'
GROUP BY u.username
CR:http://jhcislpg.blogspot.com/2015/09/user.html#more