开发者

Generating columns based on query results

I have four pivots I'm pulling data from that look like this:

tracked_work_by_users_by_operation_pivot:
+-------------------+---------------+---------------+-----------------------+---------------+
|  DATE(start_time) | userid        | operation_id  | Time estimated        | Time Elapsed  |
+-------------------+---------------+---------------+-----------------------+---------------+
| 1/2/2011-1/8/2011 | jsmith        |             11|                     40|             40|
| 1/2/2011-1/8/2011 | jsmith        |             10|                     20|             24|
+-------------------+---------------+---------------+-----------------------+---------------+

faults_by_user_pivot:
+-------------------+---------------+---------------+-----------------------+----------+
| date(date_entered)| userid        | operation_id  | Major                 | Minor    |
+-------------------+---------------+---------------+-----------------------+----------+
| 1/2/2011-1/8/2011 | jsmith        |             11|                     2 |         1|
+-------------------+---------------+---------------+-----------------------+----------+

paid_hours_by_user_pivot:
+-------------------+---------------+---------+
|date_range         | userid        | Total   |
+-------------------+---------------+---------+
| 1/2/2011-1/8/2011 | jsmith        | 40      |
+-------------------+---------------+---------+


tracked_work_by_users_pivot:
+-------------------+---------------+---------+
|DATE(start_time)   | userid        | Total   |
+-------------------+---------------+---------+
| 1/2/2011-1/8/2011 | jsmith        | 24      |
|                   |               |         |
+-------------------+---------------+---------+

What I need to do is compile a report for each user for each operation. From what I see the best way to do that is to have a format similar to:

                     +--------------+--------------+                                              +--------------+--------------+
                     |    jsmith    |  packaging   |                                              |  jsmith      |  machining   |
+---------------开发者_Go百科-----+--------------+--------------+----------------+--------------+--------------+--------------+--------------+----------------+--------------+--------------+
|    DATE            | time_elapsed | hours_worked | estimated_work | minor_faults | major_faults | time_elapsed | hours_worked | estimated_work | minor_faults | major_faults |
+--------------------+--------------+--------------+----------------+--------------+--------------+--------------+--------------+----------------+--------------+--------------+
| 1/2/2011-1/8/2011  |           24 |           40 |             36 |            1 |            2 |           24 |           40 |             36 |            1 |            2 |              
+--------------------+--------------+--------------+----------------+--------------+--------------+--------------+--------------+----------------+--------------+--------------+

So that jsmith will have separate entries for machining and for packaging because we want to be able to rank him against all machining operators and all packaging operators. How can I best do this so that I will not have to add another 12 entries(since there are twelve operations) every time I add a new user?


The best way to do this seems to be to simply write an app instead of strong-arming excel into it.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜