How to query employee details and relate their performance metrics?

Question

I am fetching the id, first name, and last name of all employees that are approved and not archived. Then I am looping these results and using the ids to query other tables to collect some count data.

I tried the below code, but I am not getting the expected output.

$queryEmp = "
    SELECT id, firstname, lastname
    FROM tbl_employee as e
    WHERE is_archive=0 and is_approved=1
";
$getQuery= $this->db->query($queryEmp);
$result= $getQuery->result();
foreach ($result as $key=> $value) {
    //echo "<pre>";
    print_r($value);

    $day = "MONTH(date_of_created) = DATE(CURRENT_DATE())";
    $group = "f_id IN (SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid)";
    $condiion = "and ba.createdby='" . $value->id . "' and " . $day ." and " . $group;
    $query2 = "
        select
            (SELECT COUNT(c_id)
             FROM tbl_lead
             WHERE leadstatus='1' AND ".$day.") as confirmCount,

            (SELECT COUNT(f_id)
             FROM tbl_fileStatus as fs
             join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
             WHERE fs.f_filestatus=1 " . $condiion . ") as disbursed,

            (SELECT COUNT(f_id)
             FROM tbl_fileStatus as fs
             join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
             WHERE fs.f_filestatus=2 ".$condiion.") as filesubmit
    ";
    # code...
    $getQuery2= $this->db->query($query2);
    $result2[]=$getQuery2->result();
}
echo "<pre>";
print_r(result2);

$result looks like this:

Array (
    [0] => stdClass Object (
        [id] => 1
        [firstname] => xyz
        [lastname] => xyz
    )
    ...
)

Second query output:

Array (
    [0] => Array (
        [0] => stdClass Object (
            [fallowCall] => 0
            [confirmCount] => 0
            [disbursed] => 0
            [filesubmit] => 0
        )
    )
    ...
)

How can I produce the correct results which relate respective employees with with their performance metrics? Either this structure:

Array (
    [0] => stdClass Object (
        [id] => 1
        [firstname] => xyz
        [lastname] => xyz
        [somename] => (
            [fallowCall] => 0
            [confirmCount] => 0
            [disbursed] => 0
            [filesubmit] => 0
        )
    )
    ...
)

Or this structure:

Array (
    [0] => stdClass Object (
        [id] => 1
        [firstname] => xyz
        [lastname] => xyz
        [fallowCall] => 0
        [confirmCount] => 0
        [disbursed] => 0
        [filesubmit] => 0
    )
    ...
)

I have added the my table structure and some sample data here: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0

some notes here

1) createdby is the id of table tbl_employee

2) lead_id in the bank table is the c_id of the table tbl_lead

3) f_bankid in the tbl_fileStatus is the bank_id of the table tbl_bankdata

@mickmackusa, "multidimensional structure? " because I am getting the employee details and in the second query I have some details related to the employee and all the details I have to display in the list. So I thought that my expected can help me to solve — user9437856
– user9437856, Commented May 24, 2020 at 11:04
Till now I have zero counts. This is an example. about somename, I thought it will be a good way to do. I can use this output also [0] => stdClass Object ( [id] => 1 [firstname] => xyz [lastname] => xyz [fallowCall] => 0 [confirmCount] => 0 [disbursed] => 0 [filesubmit] => 0 ) — user9437856
– user9437856, Commented May 24, 2020 at 11:31
@mickmackusa, In your first query I am getting the error #1242 - Subquery returns more than 1 row.I think if we go with array then it will be good and there are more changes to increse the subquery in the future. — user9437856
– user9437856, Commented May 24, 2020 at 13:09
@mickmackusa, I created the table strcture and updated in the question. — user9437856
– user9437856, Commented May 25, 2020 at 4:19
Please provide enough fiddle data to generate non-zero counts. Having a mix of qualifying data and non-qualifying data for intended queries makes verifying a "working solution" easier to test/visualize. (Then update the demo link.) Also please delete old comments under this question that have no future value to researchers/volunteers. My workday ends in 3 hours, I'll check back then. — mickmackusa
– mickmackusa ♦, Commented May 25, 2020 at 4:25

mickmackusa · Accepted Answer · 2020-06-01 21:14:51Z

There is actually no need to create the additional depth/complexity just to hold the count data. Furthermore, by using a combination of LEFT JOINs to connect the related tables and apply your required conditional rules, you can achieve your desired result by making just one trip to the database. This will without question provide superior efficiency for your application. LEFT JOINs are important to use so that counts can be zero without excluding employees from the result set.

Also, I should point out that your attempted query was mistakenly comparing a MONTH() value against a DATE() value -- that was never going to end well. :) In fact, to ensure that your sql is accurately isolating the current month from the current year, you need to be also checking the YEAR value.

My recommended sql:

SELECT
    employees.id,
    employees.firstname,
    employees.lastname,
    COUNT(DISTINCT leads.c_id) AS leadsThisMonth,
    SUM(IF(fileStatus.f_filestatus = 1, 1, 0)) AS disbursedThisMonth,
    SUM(IF(fileStatus.f_filestatus = 2, 1, 0)) AS filesubmitThisMonth
FROM tbl_employee AS employees

LEFT JOIN tbl_lead AS leads
    ON employees.id = leads.createdby
        AND leadstatus = 1 
        AND MONTH(leads.date_of_created) = MONTH(CURRENT_DATE())
        AND YEAR(leads.date_of_created) = YEAR(CURRENT_DATE())                                                 

LEFT JOIN tbl_bankdata AS bankData
    ON employees.id = bankData.createdby

LEFT JOIN tbl_fileStatus AS fileStatus
    ON bankData.bank_id = fileStatus.f_bankid
        AND MONTH(fileStatus.date_of_created) = MONTH(CURRENT_DATE())
        AND YEAR(fileStatus.date_of_created) = YEAR(CURRENT_DATE())
        AND fileStatus.f_id = (
                SELECT MAX(subFileStatus.f_id)
                FROM tbl_fileStatus AS subFileStatus
                WHERE subFileStatus.f_bankid = bankData.bank_id
                GROUP BY subFileStatus.f_bankid
            )

WHERE employees.is_archive = 0
    AND employees.is_approved = 1

GROUP BY employees.id, employees.firstname, employees.lastname

The SUM(IF()) expression is a technique used to execute a "conditional count". "Aggregate data" is formed by using GROUP BY and there are specialized "aggregate functions" which must be used to create linear/flat data from these clusters/non-flat collections of data. fileStatus data is effectively piled up upon itself due to the GROUP BY call. If COUNT(fileStatus.f_filestatus) was called, it would count all of the rows in the cluster. Since you wish to differentiate between f_filestatus = 1 and f_filestatus = 2, an IF() statement is used. This is doing the same thing as COUNT() (adding 1 for every qualifying occurrence), but it is different from COUNT() in that it does not count specific rows (within the scope of the cluster) unless the IF() expression is satisfied. Another example.

Here is a db fiddle demo with some adjustments to your supplied sample data: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/4 (The result set will only be "good" while the current is June of this year.)

After saving the above string as $sql, you can simply execute it and loop through the array of objects like this:

foreach ($this->db->query($sql)->result() as $object) {
    // these are the properties available in each object
    // $object->id
    // $object->firstname
    // $object->lastname
    // $object->leadsThisMonth
    // $object->disbursedThisMonth
    // $object->filesubmitThisMonth
}

Thank you so much for your answer and I really appreciate your support and guide for the better question. the answer is working for me. I haven't understood this fileStatus.f_filestatus = 1, 1, 0). if possible can you share any link where i can learn this

Collectives™ on Stack Overflow

How to query employee details and relate their performance metrics?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related