How to Query Employee Details and Relate Their Performance Metrics

How to query employee details and relate their performance metrics?

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
}

Restricting a SQL query so that any particular value in a certain column can only appear 3 times in the results, with respect to a given ordering

I have managed to find an answer myself. It seems to work by pairing each record up with all of the records from the same person that were equal or greater, and then choosing only the (left) records that had no more than 3 greater-or-equal pairings.

SELECT P.Name, P.Month, P.Sales, P.Commendations, P.Absences
FROM Performance P
LEFT JOIN Performance P2 ON (P.Name = P2.Name AND P.Sales <= P2.Sales)
GROUP BY P.Name, P.Month, P.Sales, P.Commendations, P.Absences
HAVING COUNT(*) <= 3
ORDER BY P.Sales DESC;

I will give the credit to a_horse_with_no_name for adding the tag "greatest-n-per-group", as I would have had no idea what to search for otherwise, and by looking through other questions with this tag I managed to find what I wanted.

I found this question that was similar to mine... Using LIMIT within GROUP BY to get N results per group?

And I followed this link that somebody had included in a comment... https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

...and the answer I wanted was in the first comment on that article. It's perfect as it uses only a LEFT JOIN, so it will work in SQLite.

Here is my SQL Fiddle: http://sqlfiddle.com/#!7/580f0/5/0

How do you measure the performance of a stored procedure?

Look at this article: Measuring SQL Performance

If you don't want to register to free account, here is a solution 1:

DECLARE @start datetime, @stop datetime
SET @start = GETDATE()
EXEC your_sp
SET @stop = GETDATE()

2nd:

SET STATISTICS TIME ON
EXEC your_sp

3rd:

SET STATISTICS IO ON
EXEC your_sp

Btw, this site has some nice articles. I'd recommend to register. It's free.

How do I get all the records in the view from foreach in controller?

You are always overwriting the data with the last content, build an array of data and assign that instead...

$allData = [];
foreach ($getMemberDetails['getAllMember'] as $m_id) {
$allData[] = $this->Member_model->getAllData($m_id->member_id)[0];
}
$getMemberDetails['getAllData'] = $allData;

I'm assuming getAllData() is returning an array of matching records, so even though there is only 1 record it will still return an array. Using [0] means to extract the 1 record your expecting.



Related Topics



Leave a reply



Submit