View Full Version : Mysql querie help for a beginner
11-22-2011, 06:23 AM
I am trying to get result and display them in a particular format. Iím a beginner and hope below discription is clear.
Table Structure is like mentioned below:
ID| Date | Report| Start time | End Time
REPORT Attribute hold any of the three values(Productivity, Monitoring, Break). I want to display the output in below two formats between two selected dates from a form.
a) DATE | Productivity | Monitoring | break| Start Time | End Time.
The report field has the paramenters productivity, Monitoring and Break. If the value has productivity, then it should display in Productivity column, Monitoring value should be displaed in monitoring field. Iím using PHP and Mysql.
b) DATE | Productivity | Monitoring | break| Start Time | End Time.
Here, I want the optput same a above however,the Start time and End time should display the sum of time between the selected dates.
11-22-2011, 06:43 AM
Ummmm...your (a) and (b) lines are 100% identical. So why do you say "Here, I want..." How can we tell the difference between (a) and (b)???
Did you mean that (b) should read:
FirstDate | LastDate | Productivity | Monitoring | break| TotalTime
If so, would there then be 3 rows?? Example:
FirstDate | LastDate | Productivity | Monitoring | break | TotalTime
11/1/2011 | 11/15/2011 | X | | | 3 days, 14.7 hours
11/1/2011 | 11/15/2011 | | X | | 5 days, 22.1 hours
11/1/2011 | 11/15/2011 | | | X | 7 days, 1.3 hours
It would really help if you would show examples of exactly what you want.
11-22-2011, 07:29 AM
Yes, there should be three lines as you mentioned. The difference between A) and b) is that a) display only the data for time as it is saved in database where as b) should calculate sum of the time between the selected dates.
b) is a monthly report for my data and a) is daily report. So b) should display the sum of the time spent on that particular report.
11-22-2011, 07:45 AM
My point was that if you show the TOTAL TIME you can *NOT* show the starttime and endtime, because there would be many many start and end times in a month.
And if this is a report for a full month, then you don't really need the START DATE and END DATE columns.
Look, if you can't go to the trouble of making a SAMPLE REPORT then it's hard for me to keep guessing what you want.
MAKE A SAMPLE.
For example, WHY do you even NEED three rows in the monthly report?
Why couldn't the monthly report just look something like this:
Report for Month of October, 2011
Total Productiviity Time | Total Monitoring Time | Total Break Time
17 days, 8.75 hours | 11 days, 3.25 hours | 2 days, 12.00 hours
BUT YOU NEED TO DESIGN THE REPORT or we can't help you do the query.
11-22-2011, 09:34 AM
Thanks for your fast reply.
Below I've posted the exact format that I need for daily report and monthly report. You're absolutely right, I don't need the start and end time for monthly report. Agent can have more than one production and training hours daily. The production, Training and Meeting should be the sum of that days.
Below is the daily report
Date | Emp ID | Emp Name | Report | Frequency | Language | Production Training | Meeting| Feedback | Others | Time Utilization (Endtime - start time)
11/22/2011 | 450220 | Messi | client Name | daily/weekly |english | 0:30| 0.30 |0.30 | 1:30
11/22/2011 | 450223 | Thomas | client Name | daily/weekly | english |0:45 |0: 30 | 0:15| | 2:00
11/22/2011 | 4502269 | Lisa |client Name | daily/weekly | Spanish | 4:00 |2:00| 2:00|8:00
Monthly report will be the same as it only display the montly time for "time Utilization". However, the time utilization should be in "hours: Minutes" and not in Days:Hours: Minutes.
11-22-2011, 09:41 PM
WOW! Completely different than what you led me to believe in your first post!
See how a *real* example clears everything up?
But now you throw in *ANOTHER TABLE*.
You said the reporting table has these fields:
ID| Date | Report| Start time | End Time
I *assume* that ID there is the employee it?
And it is a foreign key to an employees table?
I will assume that the employees table has a structure somewhat like
ID | EmployeeName
But where do the fields Report, Frequency, and Language come from???
Some third table?
I can't help if you don't show me the structure of *ALL* the tables involved and how they are linked together (primary keys and foreign keys).
Finally, in the example you gave you have 11 field *names* listed, but only 10 field *values* given. You don't show a value for the field Time Utilization.
11-23-2011, 09:48 AM
The table format is the exact one I needed. Since, it is more complicated, I thought just getting the date calculated and different fields values. Since you asked for the format, I posted the Structure that I've . If you help me with first one, then I'll be able to complete the remaining things.
11-23-2011, 02:01 PM
when asking for help it is best to show your actual table structure (sample data would be fine if what you have in your real tables is sensitive data) and exactly what you are trying to achieve.
that way people don't spend time helping you solve something that isn't your real problem.
It also helps you get your needed solution faster.