Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Unanswered: Help with Calculated Fields into Rows

    I am looking for some help in getting some data from a MySQL table into a new format. My ultimate goal is to have a table with a record for each hour of each day (0-23) with the net sales achieved during that hour, the cumulative net sales for that day so far, the wages earned by employees during that hour, and the cumulative wages earned by employees so far during that day.

    The data is employee time and wages tracking and is formatted as follows:

    name |date_in | time_in | date_out | time_out | hours | hourly_rate | total_pay
    Smith | 7-7-12 | 07:51:00| 7-7-12 | 15:22 | 7.52 | 13.50 | 101.52
    Scott | 7-6-12 | 10:10:00| 7-6-12 | 17:02 | 6.87 | 13.50 | 92.75

    I also have sales data in a separate table that breaks down the sales by transaction and then sums it up to the cumulative net sales for the day.
    date | hour | net_sale | cumulative_net_sales
    7-6-12 | 8 | 2.25 | 2.25
    7-6-12 | 8 | 5.00 | 7.25
    7-6-12 | 9 | 1.00 | 8.25

    I am trying to get the data into a table that looks like this:
    date | hour | net_sales | wages | cumulative_net_sales | cumulative_wages
    7-6-12 | 8 | 7.25 | 13.50 | 7.25 | 13.50
    7-6-12 | 9 | 1.00 | 13.50 | 8.25 | 27.00

    What I am trying to do is be able to chart this data. But my problem is that while the sales data is broken down with an exact time that the transaction occurred, the wages table (at top) is not. I can easily extract the hour from the transactions data and cumulatively sum the values. Thus far, I have been able to get the wages data broken down by hour in calculated columns – a column for each record showing how much that employee was paid during the 8am hour, the 9am hour, and so on. But the problem is that I don’t know how to make each of those columns a row. I need a record for each hour of each day. I can achieve this with the transactions data but have not figured out a way to do it with the wages data.

    Any help would be very much appreciated.
    Thank you.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you remove any values that are derived from the tables (i.e. total-pay and anythng cumulative).

    I need a record for each hour of each day.
    You need the value, but not a separate row/column.

    You may want to consider using an average hourly rate for pay.

  3. #3
    Join Date
    Jul 2012
    Posts
    3
    Thank you for your response. While I've worked a bit with databases in the past, it's been awhile. I know I'm not doing everything the best possible way, but the database is relatively small and I'm trying to make it work for the time being.

    Quote Originally Posted by papadi View Post
    Suggest you remove any values that are derived from the tables (i.e. total-pay and anythng cumulative).
    Are you able to explain a bit more? I want to be able to graph this data and the only way I've been able to visualize it is by having the records for every hour of every day like I had noted. Then, in order to graph the cumulative sales or wages up through that hour of the day, I thought I would need the cumulative field.

    Quote Originally Posted by papadi View Post
    You need the value, but not a separate row/column.
    Again, I'm not quite sure I know what you mean. If not in a separate row, where would I put it?

    Quote Originally Posted by papadi View Post
    You may want to consider using an average hourly rate for pay.
    Unfortunately, the data is being pulled from a third party app and I've got to work with it as it is.

    Thank you very much for your interest in my project and for your help thus far.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486

    Help with Calculated Fields into Rows

    Originally Posted by papadi
    Suggest you remove any values that are derived from the tables (i.e. total-pay and anythng cumulative).


    Are you able to explain a bit more? I want to be able to graph this data and the only way I've been able to visualize it is by having the records for every hour of every day like I had noted. Then, in order to graph the cumulative sales or wages up through that hour of the day, I thought I would need the cumulative field.
    I'm not quite sure I know what you mean. If not in a separate row, where would I put it?
    The derived values would be determined at "presentation time", not stored permanently.

    Unfortunately, the data is being pulled from a third party app and I've got to work with it as it is.
    Yup, this is becoming more and more common. . . We just have to work with what is available for our use. I believe you can calculate an average given the information posted about the data you receive. This too would be done "on the fly" and not stored in the database.

  5. #5
    Join Date
    Jul 2012
    Posts
    3
    That's a good idea - I'll look into displaying the derived values at presentation time.

    I still think (and perhaps this is because I'm not so entrenched in it that I can't see it from another perspective) I need to be able to have the data show as below in order to graph it properly:
    date | hour | net_sales | wages
    7-7-12 | 8 | 45.00 | 13.50
    7-7-12 | 9 | 102.00 | 27.00
    7-7-12 | 10 | 212.00 | 27.00
    ...
    7-7-12 | 22 | 96.00 | 13.50

    In the above, it simply is denoting that during the 8am hour the store brought in $45 of net sales while paying out $13.50 in wages for that hour. And in the 9am hour, the store brought in $102 while paying out $27 in wages for that specific hour.

    That's why I'm trying to break down the data into the hourly rows. And that's what I need help with - I just can't figure it out.

Posting Permissions

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