28select eb_no,worker_name,MAX(CASE WHEN attdate = '2023-02-26' THEN atttypes ELSE 'A' END) AS '26/02',MAX(CASE WHEN attdate = '2023-02-27' THEN atttypes ELSE 'A' END) AS '27/02',MAX(CASE WHEN attdate = '2023-02-28' THEN atttypes ELSE 'A' END) AS '28/02',MAX(CASE WHEN attdate = '2023-03-01' THEN atttypes ELSE 'A' END) AS '01/03',MAX(CASE WHEN attdate = '2023-03-02' THEN atttypes ELSE 'A' END) AS '02/03',MAX(CASE WHEN attdate = '2023-03-03' THEN atttypes ELSE 'A' END) AS '03/03',MAX(CASE WHEN attdate = '2023-03-04' THEN atttypes ELSE 'A' END) AS '04/03',MAX(CASE WHEN attdate = '2023-03-05' THEN atttypes ELSE 'A' END) AS '05/03',MAX(CASE WHEN attdate = '2023-03-06' THEN atttypes ELSE 'A' END) AS '06/03',MAX(CASE WHEN attdate = '2023-03-07' THEN atttypes ELSE 'A' END) AS '07/03',MAX(CASE WHEN attdate = '2023-03-08' THEN atttypes ELSE 'A' END) AS '08/03',MAX(CASE WHEN attdate = '2023-03-09' THEN atttypes ELSE 'A' END) AS '09/03',MAX(CASE WHEN attdate = '2023-03-10' THEN atttypes ELSE 'A' END) AS '10/03',MAX(CASE WHEN attdate = '2023-03-11' THEN atttypes ELSE 'A' END) AS '11/03',MAX(CASE WHEN attdate = '2023-03-12' THEN atttypes ELSE 'A' END) AS '12/03',MAX(CASE WHEN attdate = '2023-03-13' THEN atttypes ELSE 'A' END) AS '13/03',MAX(CASE WHEN attdate = '2023-03-14' THEN atttypes ELSE 'A' END) AS '14/03',MAX(CASE WHEN attdate = '2023-03-15' THEN atttypes ELSE 'A' END) AS '15/03',MAX(CASE WHEN attdate = '2023-03-16' THEN atttypes ELSE 'A' END) AS '16/03',MAX(CASE WHEN attdate = '2023-03-17' THEN atttypes ELSE 'A' END) AS '17/03',MAX(CASE WHEN attdate = '2023-03-18' THEN atttypes ELSE 'A' END) AS '18/03',MAX(CASE WHEN attdate = '2023-03-19' THEN atttypes ELSE 'A' END) AS '19/03',MAX(CASE WHEN attdate = '2023-03-20' THEN atttypes ELSE 'A' END) AS '20/03',MAX(CASE WHEN attdate = '2023-03-21' THEN atttypes ELSE 'A' END) AS '21/03',MAX(CASE WHEN attdate = '2023-03-22' THEN atttypes ELSE 'A' END) AS '22/03',MAX(CASE WHEN attdate = '2023-03-23' THEN atttypes ELSE 'A' END) AS '23/03',MAX(CASE WHEN attdate = '2023-03-24' THEN atttypes ELSE 'A' END) AS '24/03',MAX(CASE WHEN attdate = '2023-03-25' THEN atttypes ELSE 'A' END) AS '25/03',
	COUNT(IF( instr(atttypes, 'P')>0 , 1, NULL)) 'Present',
	COUNT(IF(instr(atttypes, 'W')>0, 1, NULL)) 'WO',
	COUNT(IF(instr(atttypes, 'H')>0, 1, NULL)) 'H',
	count(*) 'Total',28-count(*) 'Absent',
    sum(othrs) othrs from ( select
		eb_id,
		eb_no,
		worker_name,
		attdate,
		replace(GROUP_CONCAT(atttype), ',', '') atttypes,
 	   sum(othrs) othrs
		from
		(
		select
			da.eb_id,
			wm.eb_no,
			wm.worker_name ,
			attendance_date attdate,
			'P' atttype,0 othrs
		from
			daily_attendance da
		join worker_master wm on
			da.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			attendance_date between '2023-02-26' and '2023-03-25'
			and da.is_active = 1 and da.attendance_type='R'
			and bm.branch_name like '%VTP%'
	union ALL
		select
			da.eb_id,
			wm.eb_no,
			wm.worker_name ,
			attendance_date attdate,
			'' atttype,da.working_hours othrs
		from
			daily_attendance da
		join worker_master wm on
			da.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			attendance_date between '2023-02-26' and '2023-03-25'
			and da.is_active = 1 and da.attendance_type='O'
			and bm.branch_name like '%VTP%'
	union ALL
		select
			lt.eb_id ,
			wm.eb_no,
			wm.worker_name ,
			ltd.leave_date attdate,
			'W' atttype,0 othrs
		from
			leave_tran_details ltd
		join leave_transactions lt on
			ltd.ltran_id = lt.leave_transaction_id
		join worker_master wm on
			lt.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			ltd.leave_date between '2023-02-26' and '2023-03-25'
			and bm.branch_name like '%VTP%'
	union ALL
		select
			thht.eb_id ,
			wm.eb_no,
			wm.worker_name ,
			hm.holiday_date attdate,
			'H' atttype,0 othrs
		from
			tbl_hrms_holiday_transactions thht
		join holiday_master hm on
			thht.holiday_id = hm.id
		join worker_master wm on
			thht.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			hm.holiday_date between '2023-02-26' and '2023-03-25'
			and bm.branch_name like '%VTP%' ) g
	group by
		eb_id,
		eb_no,
		worker_name,
		attdate order by eb_no,attdate) h group by eb_no,worker_name