쿼쿼리리

select o.email as email
, '회사명' as company
, u.name as name
, month(o.overtime_date) as month
, sum(o.overtime_hours) as total
, MAX(case when day(o.overtime_date) = 1 then o.overtime_hours when day(d.day_off_date) = 1 then '해피휴가' else 0 end ) as d1
, MAX(case when day(o.overtime_date) = 2 then o.overtime_hours when day(d.day_off_date) = 2 then '해피휴가' else 0 end ) as d2
, MAX(case when day(o.overtime_date) = 3 then o.overtime_hours when day(d.day_off_date) = 3 then '해피휴가' else 0 end ) as d3
, MAX(case when day(o.overtime_date) = 4 then o.overtime_hours when day(d.day_off_date) = 4 then '해피휴가' else 0 end ) as d4
, MAX(case when day(o.overtime_date) = 5 then o.overtime_hours when day(d.day_off_date) = 5 then '해피휴가' else 0 end ) as d5
, MAX(case when day(o.overtime_date) = 6 then o.overtime_hours when day(d.day_off_date) = 6 then '해피휴가' else 0 end ) as d6
, MAX(case when day(o.overtime_date) = 7 then o.overtime_hours when day(d.day_off_date) = 7 then '해피휴가' else 0 end ) as d7
, MAX(case when day(o.overtime_date) = 8 then o.overtime_hours when day(d.day_off_date) = 8 then '해피휴가' else 0 end ) as d8
, MAX(case when day(o.overtime_date) = 9 then o.overtime_hours when day(d.day_off_date) = 9 then '해피휴가' else 0 end ) as d9
, MAX(case when day(o.overtime_date) = 10 then o.overtime_hours when day(d.day_off_date) = 10 then '해피휴가' else 0 end ) as d10
, MAX(case when day(o.overtime_date) = 11 then o.overtime_hours when day(d.day_off_date) = 11 then '해피휴가' else 0 end ) as d11
, MAX(case when day(o.overtime_date) = 12 then o.overtime_hours when day(d.day_off_date) = 12 then '해피휴가' else 0 end ) as d12
, MAX(case when day(o.overtime_date) = 13 then o.overtime_hours when day(d.day_off_date) = 13 then '해피휴가' else 0 end ) as d13
, MAX(case when day(o.overtime_date) = 14 then o.overtime_hours when day(d.day_off_date) = 14 then '해피휴가' else 0 end ) as d14
, MAX(case when day(o.overtime_date) = 15 then o.overtime_hours when day(d.day_off_date) = 15 then '해피휴가' else 0 end ) as d15
, MAX(case when day(o.overtime_date) = 16 then o.overtime_hours when day(d.day_off_date) = 16 then '해피휴가' else 0 end ) as d16
, MAX(case when day(o.overtime_date) = 17 then o.overtime_hours when day(d.day_off_date) = 17 then '해피휴가' else 0 end ) as d17
, MAX(case when day(o.overtime_date) = 18 then o.overtime_hours when day(d.day_off_date) = 18 then '해피휴가' else 0 end ) as d18
, MAX(case when day(o.overtime_date) = 19 then o.overtime_hours when day(d.day_off_date) = 19 then '해피휴가' else 0 end ) as d19
, MAX(case when day(o.overtime_date) = 20 then o.overtime_hours when day(d.day_off_date) = 20 then '해피휴가' else 0 end ) as d20
, MAX(case when day(o.overtime_date) = 21 then o.overtime_hours when day(d.day_off_date) = 21 then '해피휴가' else 0 end ) as d21
, MAX(case when day(o.overtime_date) = 22 then o.overtime_hours when day(d.day_off_date) = 22 then '해피휴가' else 0 end ) as d22
, MAX(case when day(o.overtime_date) = 23 then o.overtime_hours when day(d.day_off_date) = 23 then '해피휴가' else 0 end ) as d23
, MAX(case when day(o.overtime_date) = 24 then o.overtime_hours when day(d.day_off_date) = 24 then '해피휴가' else 0 end ) as d24
, MAX(case when day(o.overtime_date) = 25 then o.overtime_hours when day(d.day_off_date) = 25 then '해피휴가' else 0 end ) as d25
, MAX(case when day(o.overtime_date) = 26 then o.overtime_hours when day(d.day_off_date) = 26 then '해피휴가' else 0 end ) as d26
, MAX(case when day(o.overtime_date) = 27 then o.overtime_hours when day(d.day_off_date) = 27 then '해피휴가' else 0 end ) as d27
, MAX(case when day(o.overtime_date) = 28 then o.overtime_hours when day(d.day_off_date) = 28 then '해피휴가' else 0 end ) as d28
, MAX(case when day(o.overtime_date) = 29 then o.overtime_hours when day(d.day_off_date) = 29 then '해피휴가' else 0 end ) as d29
, MAX(case when day(o.overtime_date) = 30 then o.overtime_hours when day(d.day_off_date) = 30 then '해피휴가' else 0 end ) as d30
, MAX(case when day(o.overtime_date) = 31 then o.overtime_hours when day(d.day_off_date) = 31 then '해피휴가' else 0 end ) as d31
, '' as holiday
, '' as note
from overtime_inf o left join day_off_inf d on o.email = d.email and month(d.day_off_date) = month(o.overtime_date)
left join users u on u.email = o.email
/*where quarter(o.overtime_date) = 3*/
where o.overtime_date >= 20250701 AND o.overtime_date <= 20250931
group by o.email, month(o.overtime_date)
/*order by o.email, month(o.overtime_date)*/
order by u.name, month(o.overtime_date)
;