1

I have a table in MySQL Database which has the details of the user inputs.Each user will be able to input their details 3 times a day.I will have a target value for each input.By the end of the day, I want to add and calculate the entries of each inputs and try to match it with each target and try to fill a column accordingly as "TARGET ACHIEVED" or "TARGET MISSED" for each input.

For eg. My SQL Query is:

INSERT INTO employee_details(enquiry,retail, collection,booking, evaluation, test_drive, 
    home_visit, name, date,time,taget_status) 
values ('$enquiry', '$retail','$collection', '$booking', '$evaluation', '$test_drive', 
    '$home_visit', '$user_name', CURDATE(), '$time');

So,when the user inputs the value of enquiry 3 times a day, then I want to calculate the sum of the 3 inputs for the column "enquiry" and check if it has achieved the target i.e. 10. If the sum is equal to 10, then the column "target_status" should be updated as "TARGET ACHIEVED" or "TARGET MISSED" accordingly. And I want to do the calculation everday by the End of the day.

Can anyone please suggest a solution on how to proceed.

M. Eriksson
  • 13,450
  • 4
  • 29
  • 40
Pranami
  • 27
  • 1
  • 10
  • Can you elaborate the question and state what you have tried until now? – Ruby Nanthagopal Feb 02 '17 at 11:18
  • @Ruby I have a table which will have all the details of the user inputs.And the user will input the detail in each field 3 times a day. So, I want to add the inputs for a particular field ,for eg For the column "enquiry" in the above mentioned table, the user will input data 3 times a day. And the target will be set to 10.And by the end of the day, i need to sum the 3 inputs for enquiry and check if it reaches 10. And I will update the "target_status" column depending on that sum which will be executed at night everyday, so that we can check if the target has been achieved. – Pranami Feb 02 '17 at 11:40

2 Answers2

0

Maybe, in your case, you should not wait the end of the day to set your target_status.

Each time you run an INSERT, and before you do it, select all where date is "today" and name equals $user_name. You immediately have:

  • the enquiry count for today: if it's 2, then you know user has just issued his 3rd enquiry for today,
  • each former enquiry value for today, so you can sum it up if your enquiry count for today is 3 (2 former + the new one you are about to insert) and immediately insert your target_status on the latest enquiry

Note that your database structure may not be appropriate for this specific case, but without changing anything, the logic I outlined should be enough.

Clorichel
  • 1,940
  • 1
  • 13
  • 24
  • I have used the following query to get the sum. SELECT SUM(enquiry) FROM employee_details WHERE name = 'merk' AND date = CURDATE() . And this query will run after the INSERT query, and it runs every time data is inserted and update the column after comparing the SUM. But I wanted to retrieve and check target_status of each employee which will be inserted at the 3rd time.I mean I want the latest status of the the sum.So,can you tell me how to proceed? – Pranami Feb 02 '17 at 12:06
0

Check this

$sql = "UPDATE employee_details SET
enquiry_sum = (SELECT SUM(enquiry) FROM (SELECT * FROM employee_details WHERE date = CURDATE() AND name = 'merk') AS x)
WHERE date = CURDATE() AND name = 'merk'";
Ruby Nanthagopal
  • 596
  • 1
  • 5
  • 17
  • I am facing an issue. If I try to show the sum of the entries for the "enquiry" column for a particular day using the below query I get the desired result. SELECT SUM(enquiry) FROM employee_details WHERE date = CURDATE() AND name = 'merk'" WHERE date = CURDATE(); But if I try to insert this value into the "enquiry_sum" column of the same table, the value is not getting updated. The query I am using is: UPDATE employee_details SET enquiry_sum = "SELECT SUM(enquiry) as enquiry_sum FROM employee_details WHERE date = CURDATE() AND name = 'merk'"; Can you please check and tell me whats wrong. – Pranami Feb 03 '17 at 06:33
  • Refer this : http://stackoverflow.com/questions/1955988/mysql-select-inside-update – Ruby Nanthagopal Feb 03 '17 at 06:37
  • The code you provided is working but there is one issue. When I input the value for enquiry, its not taking the count of the recent input to calculate the sum of enquiry, but its considering the previous values and calculating the sum. For eg, if I input the value of enquiry 3 times ,the sum gets calculated with the first and second input only, the third input is not considered. – Pranami Feb 03 '17 at 07:55
  • And also if I give an input for a new user for the first time,the sum takes the value as 0,The calculation starts only from the second input and updates the column.I want to set the sum of the first input as the same value as the input was given.Can you please suggest how to proceed? – Pranami Feb 03 '17 at 07:59