Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Aug 2011
    Posts
    101
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Question Mysql querie help for a beginner

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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:

    Code:
    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
    Or what???

    It would really help if you would show examples of exactly what you want.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Aug 2011
    Posts
    101
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Question

    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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:
    Code:
        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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Aug 2011
    Posts
    101
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Question

    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.
    Last edited by naveendk.55; 11-22-2011 at 10:02 AM. Reason: few changes

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Aug 2011
    Posts
    101
    Thanks
    7
    Thanked 0 Times in 0 Posts
    hi,

    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.

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •