ST HRM

› UPDATE `tbl_attendance` SET `late_status` = '0'

UPDATE `tbl_attendance` SET `late_status` = '0', `late_time` = '0' WHERE `card_id` = '1136'
AND `attendance_date` BETWEEN '2024-09-01' AND '2024-09-30'

› -- multiple rows in the `tbl_holiday` table with the same `holy_date` and `branch_id` combination.

SELECT * 
FROM `tbl_holiday` 
WHERE (`holy_date`, `branch_id`) IN (
    SELECT `holy_date`, `branch_id` 
    FROM `tbl_holiday` 
    GROUP BY `holy_date`, `branch_id` 
    HAVING COUNT(*) > 1
) 
ORDER BY `holy_date` ASC, `branch_id` ASC;

› -- This query will delete all but the earliest record for each `holy_date` and `branch_id` combination in the `tbl_holiday` table.

DELETE FROM `tbl_holiday`
WHERE `holiday_id` NOT IN (
    SELECT * FROM (
        SELECT MIN(`holiday_id`) 
        FROM `tbl_holiday`
        GROUP BY `holy_date`, `branch_id`
    ) AS keep_records
);

› SELECT * FROM attendance_log

SELECT * FROM attendance_log WHERE checktime BETWEEN '2024-09-01' AND '2024-09-02';

› -- This query will update the `late_time` column in the `tbl_attendance` table for a specific `card_id` and date range, calculating the difference in minutes between `in_time` and `first_in_time`.

UPDATE `tbl_attendance`
SET `late_time` = TIMESTAMPDIFF(MINUTE, `in_time`, `first_in_time`)
WHERE `card_id` = '1111'
AND `attendance_date` BETWEEN '2024-09-01' AND '2024-09-30'
AND `first_in_time` IS NOT NULL
AND `in_time` IS NOT NULL;

› -- #for `late_time` calculation

UPDATE `tbl_attendance`
SET `late_time` = GREATEST(0, TIMESTAMPDIFF(MINUTE, `in_time`, `first_in_time`))
WHERE `schedule_id` = 6 AND `attendance_date` = '2025-06-15';

› #for `late_status` Update

UPDATE `tbl_attendance`
SET `late_status` = 0
WHERE late_time < 1 AND `schedule_id` = 6 AND `attendance_date` = '2025-06-15';

› -- This query will update the `early_going` column in the `tbl_attendance` table for a specific `schedule_id`, setting it to 0 for records where `out_time` is greater than '18:00:00' and `attendance_date` is after '2024-07-01'.

UPDATE tbl_attendance SET early_going = 0 WHERE schedule_id = 1 AND early_going = 1 AND out_time > '18:00:00' AND attendance_date > '2024-07-01';

› -- This query will update the `out_time` column in the `tbl_attendance` table for records where `temp_in_time` is greater than `out_time` and `attendance_date` is after '2024-07-01'.

UPDATE tbl_attendance
SET out_time = temp_in_time
WHERE temp_in_time > out_time
AND attendance_date > '2024-07-01';

› -- This query will update the `staff_id` column in the `tbl_employee` table, removing any hyphens from the `staff_id` values.

UPDATE tbl_employee 
SET staff_id = REPLACE(staff_id, '-', '');

› -- This query will update the `staff_id` column in the `tbl_employee` table, setting it to the value of the `card_id` column.

UPDATE tbl_employee 
SET card_id = machine_id;

› -- This query will update the `staff_id` column in the `tbl_employee` table, setting it to the value of the `card_id` column.

UPDATE tbl_employee 
SET staff_id = card_id;

› -- This query will select the `employee_id`, `card_id`, `machine_id`, and `employee_type` columns from the `tbl_employee` table.

SELECT employee_id, card_id, machine_id,employee_type
FROM tbl_employee;

› -- This query will delete records from the `tbl_m_salary_generate` table where the `branch_id` is 19 and the `month` column matches '2024-05'.

DELETE FROM tbl_m_salary_generate WHERE branch_id = 19 AND month LIKE '2024-05';

› -- This query will select all records from the `tbl_m_salary_generate` table where the `branch_id` is 19 and the `month` column matches '2024-05'.

SELECT * FROM tbl_m_salary_generate WHERE branch_id = 19 AND month LIKE '2024-05'

› -- This query will update the `staff_id` column in the `tbl_employee` table, setting it to the value of the `card_id` column for records where the `branch_id` is 26.

UPDATE tbl_employee 
SET staff_id = card_id
 WHERE branch_id = 26;

› -- This query will truncate (delete all data from) the specified tables in the database.

TRUNCATE tbl_attendance_log; 
TRUNCATE tbl_attendance; 
TRUNCATE tbl_holiday; 
TRUNCATE tbl_m_salary_generate; 
TRUNCATE tbl_employee; 
TRUNCATE tbl_position; 
TRUNCATE tbl_branch_permission; 
TRUNCATE tbl_leave_application; 
TRUNCATE meal_deduction; 
TRUNCATE tbl_salary; 
TRUNCATE tbl_leave_type; 
TRUNCATE tbl_department; 
TRUNCATE tbl_section; 
TRUNCATE tbl_concern_branch; 
TRUNCATE tbl_grade; 
TRUNCATE tbl_schedule; 
TRUNCATE tbl_company_concern; 
TRUNCATE meal;

› -- This query will select all records from the `attendances` table where the `status` is 0, grouping by `user_id` and ordering by `user_id` in ascending order.

SELECT * FROM `attendances` WHERE `status` = 0 GROUP BY `user_id` ORDER BY `user_id` ASC;

› -- late_time and status Update for tbl_attendance table 10 min

UPDATE tbl_attendance
SET 
    late_time = CASE 
        WHEN TIMESTAMPDIFF(SECOND, in_time, first_in_time) > 600 
        THEN TIMESTAMPDIFF(MINUTE, in_time, first_in_time) - 10
        ELSE 0 
    END,
    late_status = CASE 
        WHEN TIMESTAMPDIFF(SECOND, in_time, first_in_time) > 600 THEN '1'
        ELSE '0' 
    END
WHERE in_time IS NOT NULL 
  AND first_in_time IS NOT NULL
  AND attendance_date LIKE '2026-05-%';

› যেহেতু অফিস টাইম 09:00:00, তাই 960 seconds (16 minutes) এর বেশি হলে late করতে হবে। -- late_time and status Update for tbl_attendance table 10 min

UPDATE tbl_attendance
SET 
    late_time = CASE 
        WHEN TIMESTAMPDIFF(SECOND, in_time, first_in_time) > 960 
        THEN TIMESTAMPDIFF(MINUTE, in_time, first_in_time) - 15
        ELSE 0 
    END,
    late_status = CASE 
        WHEN TIMESTAMPDIFF(SECOND, in_time, first_in_time) > 960
        THEN '1'
        ELSE '0' 
    END
WHERE in_time IS NOT NULL 
  AND first_in_time IS NOT NULL
  AND attendance_date LIKE '2026-05-%';

Categories