attendance overview
🧩 Syntax:
WITH
date_series AS (
SELECT generate_series(
date_trunc('month', TO_DATE('2025-05', 'YYYY-MM'))::date,
(date_trunc('month', TO_DATE('2025-05', 'YYYY-MM')) + interval '1 month - 1 day')::date,
interval '1 day'
)::date AS date
)
,
calendar_info AS (
SELECT
ds.date,
date_trunc('week', ds.date)::date AS week_start,
(date_trunc('week', ds.date) + interval '6 days')::date AS week_end,
date_trunc('month', ds.date)::date AS month_start,
(date_trunc('month', ds.date) + interval '1 month - 1 day')::date AS month_end,
CASE
WHEN ds.date < MIN(CASE WHEN EXTRACT(DOW FROM ds.date) = 1 THEN ds.date END) OVER () THEN 1
ELSE FLOOR((ds.date - MIN(CASE WHEN EXTRACT(DOW FROM ds.date) = 1 THEN ds.date END) OVER ()) / 7) + 2
END AS week_of_month
FROM date_series ds
)
,
attendance_logs AS (
SELECT
eal.date,
eal.employee_id,
MIN(eal.in_time AT TIME ZONE 'UTC') AS min_in_time,
MAX(eal.out_time AT TIME ZONE 'UTC') AS max_out_time,
MAX(eal.in_time AT TIME ZONE 'UTC') AS max_in_time,
BOOL_OR(eal.in_time IS NULL OR eal.out_time IS NULL) AS has_incomplete_logs,
json_agg(
json_build_object(
'in_time', eal.in_time,
'out_time', eal.out_time,
'status_type','present'
) ORDER BY eal.in_time ASC
) AS log_entries
FROM employee_attendance_logs eal
WHERE eal.employee_id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
AND eal.is_deleted = false
GROUP BY eal.date, eal.employee_id
)
,
leave_info AS (
SELECT
lrt.transaction_date,
json_agg(
json_build_object(
'leave_type', lt.leave_type,
'change_duration', lrt.change_duration
)
)::json AS leave_details
FROM "leave_requests" lr
JOIN calendar_info ci ON ci.date = date_trunc('month', TO_DATE('2025-05-01', 'YYYY-MM'))::date
INNER JOIN "leave_request_transaction" lrt
ON lr.id = lrt.leave_request_id
AND lrt.transaction_date BETWEEN ci.month_start AND ci.month_end
LEFT JOIN "leave_plan_leave_types" lplt
ON lr.leave_plan_leave_type_id = lplt.id
LEFT JOIN "leave_types" lt
ON lplt.leave_type_id = lt.id
WHERE lr.employee_id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
AND ((lr.from_date BETWEEN ci.month_start AND ci.month_end)
OR (lr.to_date BETWEEN ci.month_start AND ci.month_end))
AND lr.status = 'approved'
GROUP BY lrt.transaction_date
),
attendance_details AS (
SELECT
ds.date,
esh.shift_type_id,
sta.min_half_day_hours,
sta.min_full_day_hours,
-- Work hours
CASE
WHEN aa.date IS NOT NULL
AND aa.has_incomplete_logs = false
AND sta.hours_calculation_method = 'clock_in_clock_out' THEN
COALESCE(
(
SELECT TO_CHAR(
GREATEST(
SUM(
CASE
WHEN eal.out_time < eal.in_time THEN
(eal.out_time + INTERVAL '24 hours' - eal.in_time)::interval
ELSE
(eal.out_time - eal.in_time)::interval
END
),
INTERVAL '00:00:00'
),
'HH24:MI:SS'
)
FROM "employee_attendance_logs" eal
WHERE eal.employee_id = e.id AND eal.is_deleted = false
AND eal.date = ds.date
),
'00:00:00'
)
WHEN aa.date IS NOT NULL
AND sta.hours_calculation_method = 'first_in_last_out' THEN
COALESCE(
(
SELECT TO_CHAR(
GREATEST(
SUM(
CASE
WHEN eal.out_time < eal.in_time THEN
(eal.out_time + INTERVAL '24 hours' - eal.in_time)::interval
ELSE
(eal.out_time - eal.in_time)::interval
END
),
INTERVAL '00:00:00'
),
'HH24:MI:SS'
)
FROM "employee_attendance_logs" eal
WHERE eal.employee_id = e.id AND eal.is_deleted = false
AND eal.date = ds.date
),
'00:00:00'
)
ELSE '00:00:00'
END AS work_hours,
CASE
WHEN ds.date < esh.effective_from IS NULL THEN ARRAY[]::text[]
WHEN ds.date >= '2025-05-01'::date
AND hd.holiday_date IS NULL
AND wo.day_of_week IS NULL
AND aa.date IS NULL THEN ARRAY[]::text[]
WHEN ds.date >= '2025-05-01'::date AND aa.date IS NULL THEN
CASE
WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
CASE
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'full_day' THEN ARRAY['full_day_weekly_off']
WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'first_half' THEN ARRAY['first_half_weekly_off']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'second_half' THEN ARRAY['second_half_weekly_off']
ELSE ARRAY[]::TEXT[]
END
ELSE ARRAY[]::TEXT[]
END
WHEN aa.date IS NOT NULL THEN
CASE
WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
CASE
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'full_day' THEN ARRAY['full_day_weekly_off', 'present']
WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday', 'present']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'first_half' THEN ARRAY['first_half_weekly_off', 'present']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'second_half' THEN ARRAY['second_half_weekly_off', 'present']
ELSE ARRAY['present']
END
ELSE ARRAY['present']
END
ELSE
CASE
WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
CASE
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'full_day' THEN ARRAY['full_day_weekly_off']
WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'first_half' THEN ARRAY['first_half_weekly_off', 'absent']
WHEN CASE
WHEN ci.week_of_month = 1 THEN wo.first
WHEN ci.week_of_month = 2 THEN wo.second
WHEN ci.week_of_month = 3 THEN wo.third
WHEN ci.week_of_month = 4 THEN wo.fourth
WHEN ci.week_of_month = 5 THEN wo.fifth
ELSE wo.last
END = 'second_half' THEN ARRAY['second_half_weekly_off', 'absent']
ELSE ARRAY['absent']
END
ELSE ARRAY['absent']
END
END AS attendance_status,
li.leave_details
FROM date_series ds
LEFT JOIN "employees" e ON e.id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
LEFT JOIN "employee_shift_type_history" esh ON
esh.employee_id = e.id AND
esh.effective_from <= ds.date AND
(esh.effective_to IS NULL OR esh.effective_to >= ds.date)
LEFT JOIN "shift_type_applicable_criteria" sta ON
sta.shift_type_id = esh.shift_type_id AND
sta.is_deleted = false
LEFT JOIN "employee_holiday_group_history" ehg ON
ehg.employee_id = e.id AND
ehg.effective_from <= ds.date AND
(ehg.effective_to IS NULL OR ehg.effective_to >= ds.date)
LEFT JOIN "holiday_groups" hg ON hg.id = ehg.holiday_group_id
LEFT JOIN "holiday_group_holidays" hd ON
hd.holiday_group_id = hg.id AND
hd.holiday_date = ds.date AND
hd.is_deleted = false
LEFT JOIN "employee_weekly_off_type_history" ewo ON
ewo.employee_id = e.id AND
ewo.effective_from <= ds.date AND
(ewo.effective_to IS NULL OR ewo.effective_to >= ds.date)
LEFT JOIN "weekly_off_type_config" wo ON
wo.weekly_off_type_id = ewo.weekly_off_type_id AND
wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum AND
wo.is_deleted = false
LEFT JOIN calendar_info ci ON ci.date = ds.date
LEFT JOIN attendance_logs aa ON aa.date = ds.date
LEFT JOIN leave_info li ON li.transaction_date = ds.date
),
leave_calculations AS (
SELECT
ad.date,
ad.attendance_status,
COALESCE(
(SELECT SUM(
CASE WHEN
(
leave_detail->>'leave_type') != 'unpaid'
AND NOT (ad.attendance_status @> ARRAY['present'])
THEN (leave_detail->>'change_duration')::NUMERIC
ELSE 0
END
)
FROM json_array_elements(COALESCE(ad.leave_details, '[]'::json)) AS leave_detail),
0
) AS paid_leave_duration,
COALESCE(
(SELECT SUM(
CASE WHEN
(
leave_detail->>'leave_type') = 'unpaid'
AND NOT (ad.attendance_status @> ARRAY['present'])
THEN (leave_detail->>'change_duration')::numeric
ELSE 0
END
)
FROM json_array_elements(COALESCE(ad.leave_details, '[]'::json)) AS leave_detail),
0
) AS unpaid_leave_duration
FROM attendance_details ad
),
day_status_on_logs AS (
SELECT
ad.date,
CASE
WHEN ad.work_hours::interval >= ad.min_full_day_hours
THEN 'present'
WHEN ad.work_hours::interval >= ad.min_half_day_hours
THEN 'half_day'
ELSE 'absent'
END AS day_status
FROM date_series ds
LEFT JOIN attendance_details ad ON ds.date = ad.date
)
-- final_attendance_statuses_calculation AS (
SELECT
ad.date,
CASE WHEN ad.attendance_status @> ARRAY['present'] THEN
CASE
WHEN ad.attendance_status = ARRAY['present'] THEN
CASE
WHEN dsl.day_status = 'half_day' THEN ARRAY['first_half_weekly_off', 'present']
WHEN dsl.day_status = 'absent' THEN ARRAY['absent']
ELSE ad.attendance_status
END
WHEN ad.attendance_status = ARRAY['holiday', 'present'] THEN
CASE
WHEN dsl.day_status = 'half_day' THEN ARRAY['half_holiday_off', 'present']
WHEN dsl.day_status = 'absent' THEN ARRAY['holiday']
ELSE ad.attendance_status
END
WHEN ad.attendance_status = ARRAY['full_day_weekly_off', 'present'] THEN
CASE
WHEN dsl.day_status = 'half_day' THEN ARRAY['first_half_weekly_off', 'present']
WHEN dsl.day_status = 'absent' THEN ARRAY['full_day_weekly_off']
ELSE ad.attendance_status
END
WHEN ad.attendance_status = ARRAY['first_half_weekly_off', 'present'] THEN
CASE
WHEN dsl.day_status = 'absent' THEN ARRAY['first_half_weekly_off', 'absent']
ELSE ad.attendance_status
END
WHEN ad.attendance_status = ARRAY['second_half_weekly_off', 'present'] THEN
CASE
WHEN dsl.day_status = 'absent' THEN ARRAY['second_half_weekly_off', 'absent']
ELSE ad.attendance_status
END
ELSE ad.attendance_status
END
ELSE ad.attendance_status
END AS final_attendance_status
FROM date_series ds
LEFT JOIN attendance_details ad ON ds.date = ad.date
LEFT JOIN day_status_on_logs dsl ON ds.date = dsl.date
