Mysql - Is it possible to modify this function in my database?

tuneup90
September 28, 2023
271 views
1 vote
2 Answers

I would like to modify a function in my schema. When it calculates the other_leave_count_per_staff, it’s getting the data from leave_type_table. In the table, i have a column calculate_efte, true or false (1 and 0). But in my function, i would like to add another calculation for operation, but when i change 0 to 1 in leave_type, it’s also calculating it for administration.

CREATE DEFINER=`root`@`localhost` FUNCTION `generate_efte_date_type_table`(par_date_range_type VARCHAR(45), par_property_record_key TEXT, par_date_year YEAR) RETURNS text CHARSET utf8mb4
    NO SQL
BEGIN
    DECLARE temp_date_type_factor INT DEFAULT 1; 
    DECLARE temp_number_days INT;
    DECLARE temp_number_weeks DECIMAL(10,2);
     
    SELECT DAYOFYEAR(CONCAT(par_date_year, '-12-31')) INTO temp_number_days;
    SET temp_number_weeks = temp_number_days / 7;
    IF par_date_range_type = 'daily' THEN
        SET temp_date_type_factor = temp_number_days;
    ELSEIF par_date_range_type = 'weekly' THEN
        SET temp_date_type_factor = temp_number_weeks; 
    ELSEIF par_date_range_type = 'monthly' THEN
        SET temp_date_type_factor = 12;
    ELSEIF par_date_range_type = 'quarterly' THEN
        SET temp_date_type_factor = 4;
    ELSEIF par_date_range_type = 'semi_yearly' THEN
        SET temp_date_type_factor = 2;
    END IF;

    SET @administration_other_leave_count_calculation = "`efte`.`administration_staff_count` * `efte`.`other_leave_count_per_staff`";
    SET @operation_other_leave_count_calculation = "`efte`.`operation_staff_count` * `efte`.`other_leave_count_per_staff`";

    RETURN CONCAT("(
        SELECT 
            `efte`.`property_record_key` AS `property_record_key`,
            `efte`.`property_display_name_short_code` AS `property_display_name_short_code`,
            `efte`.`property_hex_color_code` AS `property_hex_color_code`,
            IFNULL((((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, " * `efte`.`administration_staff_count`)
                - (`efte`.`daily_working_hour_administration` * (`efte`.`administration_annual_leave_count` + `efte`.`administration_ph_count` * `efte`.`ph_count` + `efte`.`administration_sh_count` * `efte`.`sh_count` + ", @administration_other_leave_count_calculation, "))) / `efte`.`administration_staff_count` / ", temp_date_type_factor, "), 0) AS `administration_working_hour`,
            IFNULL((((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, " * `efte`.`operation_staff_count`)
                - (`efte`.`daily_working_hour_operation` * (`efte`.`operation_annual_leave_count` + `efte`.`operation_ph_count` * `efte`.`ph_count` + `efte`.`operation_sh_count` * `efte`.`sh_count` + ", @operation_other_leave_count_calculation,"))) / `efte`.`operation_staff_count` / ", temp_date_type_factor, "), 0) AS `operation_working_hour`,
            (IFNULL((((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, " * `efte`.`operation_staff_count`)
                - (`efte`.`daily_working_hour_operation` * (`efte`.`operation_annual_leave_count` + `efte`.`operation_ph_count` * `efte`.`ph_count` + `efte`.`operation_sh_count` * `efte`.`sh_count` + ", @operation_other_leave_count_calculation,"))) / `efte`.`operation_staff_count` / ", temp_date_type_factor, "), 0)
            +
            IFNULL((((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, " * `efte`.`administration_staff_count`)
                - (`efte`.`daily_working_hour_administration` * (`efte`.`administration_annual_leave_count` + `efte`.`administration_ph_count` * `efte`.`ph_count` + `efte`.`administration_sh_count` * `efte`.`sh_count` + ", @administration_other_leave_count_calculation, "))) / `efte`.`administration_staff_count` / ", temp_date_type_factor, "), 0)) AS `all_working_hour`,
            IFNULL((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0) AS `administration_casual_working_hour`, 
            IFNULL((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0) AS `operation_casual_working_hour`,
            (IFNULL((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0)
            +
            IFNULL((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0)) AS `all_casual_working_hour`
        FROM 
            (SELECT 
                `p`.`record_key` AS `property_record_key`, 
                `p`.`display_name_short_code` AS `property_display_name_short_code`, 
                `p`.`hex_color_code` AS `property_hex_color_code`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' THEN 1 ELSE 0 END), 0) AS `administration_staff_count`, 
                `ewh`.`daily_working_hour_administration` AS `daily_working_hour_administration`,
                `ewh`.`weekly_working_hour_administration` AS `weekly_working_hour_administration`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' THEN `s`.`annual_leave` ELSE 0 END), 0) AS `administration_annual_leave_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' AND `s`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `administration_ph_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' AND `s`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `administration_sh_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' THEN 1 ELSE 0 END), 0) AS `operation_staff_count`,
                `ewh`.`daily_working_hour_operation` AS `daily_working_hour_operation`,
                `ewh`.`weekly_working_hour_operation` AS `weekly_working_hour_operation`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' THEN 1 ELSE 0 END), 0) AS `operation_annual_leave_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' AND `s`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `operation_ph_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' AND `s`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `operation_sh_count`,
                IFNULL(`hc`.`ph_count`, 0) AS `ph_count`,
                IFNULL(`hc`.`sh_count`, 0) AS `sh_count`,
                IFNULL(`lc`.`other_leave_count_per_staff`, 0) AS `other_leave_count_per_staff`
            FROM `staff` `s`
            JOIN `property` `p` ON `s`.`property_record_key` = `p`.`record_key` AND INSTR('", par_property_record_key, "', `p`.`record_key`)
            JOIN `employment_status` `es` ON `s`.`employment_status_record_key` = `es`.`record_key`
            JOIN `efte_working_hour` `ewh` ON `s`.`property_record_key` = `ewh`.`property_record_key`
            LEFT JOIN (
                SELECT
                    `h`.`property_record_key` AS `property_record_key`,
                    IFNULL(SUM(CASE WHEN `h`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `ph_count`,
                    IFNULL(SUM(CASE WHEN `h`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `sh_count`
                FROM
                    `holiday` `h` 
                WHERE `h`.`year_of_holiday` = '", par_date_year, "'
                GROUP BY `h`.`property_record_key`
            ) `hc` ON `s`.`property_record_key` = `hc`.`property_record_key`
            LEFT JOIN (
                SELECT `property_record_key`, SUM(`lt`.`default_days`) AS `other_leave_count_per_staff`
                FROM `leave_type` `lt`
                WHERE `calculate_efte` IS TRUE
                GROUP BY `lt`.`property_record_key`
            ) `lc` ON `s`.`property_record_key` = `lc`.`property_record_key`
            WHERE `s`.`record_status` = 'Active' AND LOWER(`es`.`permanent`) = 'yes'
            GROUP BY `s`.`property_record_key`
            ) `efte`) `efte_date_type`");
END

I tried creating a new table and creating the columns only for those shifts that i need, but when i join the tables together i get errors.

Tags: mysql sql

Answers

please try following changes on new code:

CREATE DEFINER=`root`@`localhost` FUNCTION `generate_efte_date_type_table`(par_date_range_type VARCHAR(45), par_property_record_key TEXT, par_date_year YEAR) RETURNS text CHARSET utf8mb4
    NO SQL
BEGIN
    DECLARE temp_date_type_factor INT DEFAULT 1; 
    DECLARE temp_number_days INT;
    DECLARE temp_number_weeks DECIMAL(10,2);
     
    SELECT DAYOFYEAR(CONCAT(par_date_year, '-12-31')) INTO temp_number_days;
    SET temp_number_weeks = temp_number_days / 7;
    IF par_date_range_type = 'daily' THEN
        SET temp_date_type_factor = temp_number_days;
    ELSEIF par_date_range_type = 'weekly' THEN
        SET temp_date_type_factor = temp_number_weeks; 
    ELSEIF par_date_range_type = 'monthly' THEN
        SET temp_date_type_factor = 12;
    ELSEIF par_date_range_type = 'quarterly' THEN
        SET temp_date_type_factor = 4;
    ELSEIF par_date_range_type = 'semi_yearly' THEN
        SET temp_date_type_factor = 2;
    END IF;

    -- Adjust calculations based on the value of calculate_efte
    SET @administration_other_leave_count_calculation = 
        CASE WHEN (SELECT calculate_efte FROM leave_type WHERE property_record_key = par_property_record_key) = 1 THEN
            "`efte`.`administration_staff_count` * `efte`.`other_leave_count_per_staff_modified`"
        ELSE
            "`efte`.`administration_staff_count` * `efte`.`other_leave_count_per_staff`"
        END;
        
    SET @operation_other_leave_count_calculation = 
        CASE WHEN (SELECT calculate_efte FROM leave_type WHERE property_record_key = par_property_record_key) = 1 THEN
            "`efte`.`operation_staff_count` * `efte`.`other_leave_count_per_staff_modified`"
        ELSE
            "`efte`.`operation_staff_count` * `efte`.`other_leave_count_per_staff`"
        END;

    -- Rest of your function code
    -- ...

END

this approach allows you to adgust the calculrtions within the function based on the condition you specified involving the calculate_efte column. Make sure to replace "other_leave_count_per_staff_modified" with the actual modified calculation you intend to use when calculate_efte is 1.

Look at the variables you have defined for the two cases:

    SET @administration_other_leave_count_calculation = "`efte`.`administration_staff_count` * `efte`.`other_leave_count_per_staff`";
    SET @operation_other_leave_count_calculation = "`efte`.`operation_staff_count` * `efte`.`other_leave_count_per_staff`";

They multiply some scalar value (which may be administration_staff_count or operation_staff_count, respectively) with other_leave_count_per_staff, which is computed as

IFNULL(`lc`.`other_leave_count_per_staff`, 0) AS `other_leave_count_per_staff`

based on

            LEFT JOIN (
                SELECT `property_record_key`, SUM(`lt`.`default_days`) AS `other_leave_count_per_staff`
                FROM `leave_type` `lt`
                WHERE `calculate_efte` IS TRUE
                GROUP BY `lt`.`property_record_key`
            ) `lc` ON `s`.`property_record_key` = `lc`.`property_record_key`

Your problem-statement was that if you update leave_type and set calculate_efte to true, then it modifies the administration calculation as well, which implies that some leave_type records represent operation, others represent administration. And the solution for your case is to make a differentiation between the two, like:

            LEFT JOIN (
                SELECT `property_record_key`, SUM(`ld`.`IsAdmin` * `lt`.`default_days`) AS `other_leave_count_per_staff_admin`,
                SUM((1 - `ld`.`IsAdmin`) * `lt`.`default_days`) AS `other_leave_count_per_staff_operation`,
                FROM `leave_type` `lt`
                WHERE `calculate_efte` IS TRUE
                GROUP BY `lt`.`property_record_key`
            ) `lc` ON `s`.`property_record_key` = `lc`.`property_record_key`

and adjust your variables as

    SET @administration_other_leave_count_calculation = "`efte`.`administration_staff_count` * `efte`.`other_leave_count_per_staff_admin`";
    SET @operation_other_leave_count_calculation = "`efte`.`operation_staff_count` * `efte`.`other_leave_count_per_staff_operation`";

The problem is that you might lack an IsAdmin field. But, provided that you have an expression that evaluates to 0 if it’s not an admin and 1 if it’s an admin, you could replace IsAdmin with that expression. If the given expression gets superfluous, then you could implement a stored function to compute IsAdmin. Nevertheless, the need to cover here is the differentiation between admin and operational.

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Is it possible to modify this function in my database?

Answers