Senthilkumar Posted April 26 Share Posted April 26 Dear Team, I want to calculate the engineer's average mark for every year. I am using the bellow code for calculating $where_columns = ['Year' => 'Year', 'Branch' => 'Branch']; $CurrentYear = date('Y'); $where_terms = []; foreach ($where_columns as $col => $input) { if ($_POST[$input] == !'') { $where_terms[] = "$col= '$_POST[$input]' "; } } $where = ''; if (!empty($where_terms)) { $where = 'WHERE ' . implode(' AND ', $where_terms); } else { $where = 'WHERE 1 '; } $query = "SELECT * FROM kra.mark $where"; $result = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($result)) { $Emp_Name = $row['Emp_Name']; $Emp_No = $row['Emp_No']; $Branch = $row['Branch']; $Year = $row['Year']; $Jan = $row["Jan"]; $Feb = $row["Feb"]; $Mar = $row["Mar"]; $Apr = $row["Apr"]; $May = $row["May"]; $Jun = $row["Jun"]; $Jul = $row["Jul"]; $Aug = $row["Aug"]; $Sep = $row["Sep"]; $Oct = $row["Oct"]; $Nov = $row["Nov"]; $Dec = $row["Decm"]; if($Jan == ""){ $Jan1 = 0; }else{ $Jan1 = $Jan; } if ($Feb == "") { $Feb1 = 0; } else { $Feb1 = $Feb; } if ($Mar == "") { $Mar1 = 0; } else { $Mar1 = $Mar; } if ($Apr == "") { $Apr1 = 0; } else { $Apr1 = $Apr; } if ($May == "") { $May1 = 0; } else { $May1 = $May; } if ($Jun == "") { $Jun1 = 0; } else { $Jun1 = $Jun; } if ($Jul == "") { $Jul1 = 0; } else { $Jul1 = $Jul; } if ($Aug == "") { $Aug1 = 0; } else { $Aug1 = $Aug; } if ($Sep == "") { $Sep1 = 0; } else { $Sep1 = $Sep; } if ($Oct == "") { $Oct1 = 0; } else { $Oct1 = $Oct; } if ($Nov == "") { $Nov1 = 0; } else { $Nov1 = $Nov; } if ($Dec == "") { $Dec1 = 0; } else { $Dec1 = $Dec; } if ($Year == $CurrentYear) { $currentMonth = date('M', strtotime(date('Y-m') . " -1 month")); if ($currentMonth == 'Dec') { $result2 = "Decm"; } else { $result2 = $currentMonth; } if ($row[$result2] == '') { $previous_month = date('m', strtotime(date('Y-m') . " -2 month")); } else { $previous_month = date('m', strtotime(date('Y-m') . " -1 month")); } try { $TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / $previous_month, 2); } catch (DivisionByZeroError $e) { $TotalMark = '0'; } } else { try { $TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / 12, 2); } catch (DivisionByZeroError $e) { $TotalMark = '0'; } } } The engineer will submit the data every month and has one month time to submit the data. For example, March month data he can submit up to April 30th. After that, he cannot submit the March month data. For the current year, I will check if the previous month's data is submitted or not. based on that I will sum all the values and divided it by the previous month. For the previous year, I directly summed up all the values and divided them by 12. My requirement is, that if any engineer is added in the middle of the year and starts updating the data, then the calculation should start from that particular month. In the above picture, s.No: 228 has started updating from Jul 2023. The average should sum J(un+Jul_Aug+Sep+Oct+Nov+Dec / 6) I don't know how to do this. Can anyone please help me with how to do this? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 26 Share Posted April 26 Stop storing spreadsheets in your database. Come back when you have normalized data. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 26 Author Share Posted April 26 Dear Mr.Barand, I am just storing the data month-wise. Please refer the table dump with the below link. https://drive.google.com/file/d/1kRszTxRE0GZZlQG4FIkvOQupYv-itK8e/view?usp=sharing This is an old project. Please suggest me how to modify the table structure and how to calculate the average Quote Link to comment Share on other sites More sharing options...
Barand Posted April 26 Share Posted April 26 How do you know that he started in July? How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months? In other words, what is the rule for when to include blank values in the averages and when to ignore them? [edit] BTW, there is no "branch" column the "mark" table Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 27 Author Share Posted April 27 Dear Mr.Barand, At the starting of this project I don't plan for these calculations. So I didn't store the details like when he is joined and when he is left. The branch will be fetched from user table with inner join query using employee number Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 27 Author Share Posted April 27 Dear Mr.Barand,  we can use the below query to display the name and branch from the user table. $query = "SELECT a.*, b.Name as Name, b.Branch as Branch FROM kra.mark as a inner join kra.users as b on b.Emp_No = a.Emp_No $where"; For your reference, I upload the user's table below link https://drive.google.com/file/d/1Cj8uxhX08z98qdGOmcvZxeU4B2gH31qP/view?usp=sharing I created a column Startmonth and updated it when it starts from. Once the user leaves, his account is deactivated and the Status becomes 0. If the status is 0 we can calculate up to the last updated month. Kindly suggest me how to do this  Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 27 Share Posted April 27 i hope that isn't real data with actual password hashes? you need an end date, so that you can only include data that's between the start date and the end date. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 27 Author Share Posted April 27 Dear Mr.mac_gyvr, I need to calculate the average every year (year-wise), not cumulative average. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 27 Share Posted April 27 (edited) that's not what @Barand and I are trying to get you to understand. for the example he asked about - 10 hours ago, Barand said: How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months? In other words, what is the rule for when to include blank values in the averages and when to ignore them? you need to know which if any of the empty/zero - sep, oct, nov, and dec values should be included in the sum/count to produce the average for the year in question. if the end date for that user was 2023-08, you don't included any of those empty values in the average calculation. if the end date is a value greater than 2023-08 or is an empty/null/maximum-value, you would treat some or all of these empty values as zeros and include them in the average calculation for the year in question. Edited April 27 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 27 Author Share Posted April 27 Ok. I understood. I included the end month also in the user table. Please check the new dump on the below link https://drive.google.com/file/d/1Hhcx8CX_EjdcEI-hoPAmVXbr6bLNTkLF/view?usp=sharing Quote Link to comment Share on other sites More sharing options...
Barand Posted April 27 Share Posted April 27 Now you have all the data you need to do what you wanted to do. For each mark record,j ust include the values for those months that lie within the data range and job done. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 27 Author Share Posted April 27 Dear Mr.Barand, Can you please guide me Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 27 Share Posted April 27 since your data isn't normalized, and is stored in month name columns in a single row, you will need to produce a year-month value, e.g. 2023-04, ... for each piece of data so that you can compare if it is BETWEEN the start and end dates OR you could normalize the data, and store each value in its own row in a table. the normalized data table would have columns for - id (autoincrement primary index), Emp_No, date, and amount. i also recommend that you store the dates using a DATE datatype and use 01 as the day part - year-month-01, e.g. 2023-04-10, 2024-06-01, ... Quote Link to comment Share on other sites More sharing options...
Barand Posted April 27 Share Posted April 27 Working with what you've unfortunately got now, when you read through the query results store each record in an array which has a subarray for month values. Covert the start and dates to index values for this array (0 if the start date is earlier than Jan and 11 if the end date is after Dec. Just use that portion of the array to calculate totals and averages. Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted April 27 Solution Share Posted April 27 (edited) here's some coding pointers - don't copy variables to other variables for nothing. just use the original variable(s) that data is in. don't write out code for every possible value. use a data-driven design and let the computer operate on each value for you. here's what I came up with. in your initialization section - // define the month number and names (columns) $months = [1=>"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Decm"]; // note: the reason you needed to use decm is because dec is a reserved word. you can use dec if you surround it in back-tacks in any sql statement inside the while(){...} loop - // array to hold the data for the current row $data = []; // loop over the month number and month names foreach($months as $mo=>$month) { // produce the year-month value $ym = $row['Year'] .'-'. str_pad($mo,2,'0',STR_PAD_LEFT); // test if the year-month is between the start and end dates if($ym >= $row['start'] && $ym <= $row['end']) // use whatever you have named the start and end date columns { // put the value into the data array // use zero for any empty value $data[] = $row[$month] == '' ? 0 : $row[$month]; } } // at this point, $data contains the entries to use for the current row of fetched data // examine the result echo '<pre>'; print_r($data); echo '</pre>'; // calculate the average $TotalMark = round((array_sum($data)) / count($data), 2); // examine the result echo $TotalMark; note: you should use a value like 9999-12 for the end date for a person who is still employed Edited April 27 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 28 Author Share Posted April 28 Dear Mr.mac_gyver & Mr.Barand, Thanks for your support. The above code is working properly. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.