Scott,
>I created a report that shows effiency for each associate (K.AID). I
>am trying to figure out how to use GROUP BY to AVG(Effiency) for each
>K.AID in subquery. Originally I used temporary tables (4) to figure
>out the average effiency for each K.AID. That often left hanging
>temporary tables and took long time to process information. And I also
>need to sum(dhours) on hours worked for each category and to subtract
>that from K.hours (clocked in hours) which shows the idle time for
>each K.AID. I already RTFM and STFW trying to figure out how to use
>GROUP BY to no avail. Here is the detailed information and example of
>my current database that I am working on. Any helpful insights or
>suggestions would be of a big help and appreciated..
Your schema is k -> de -> de_kpi, so a query that groups by k.id or
k.aid will not show individual row values from the 'de' table. Is the
following what you mean?
SELECT
k.id,
k.aid,
...,
AVG(CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)))
AS 'Avg Effiency',
k.hours - SUM(de.dhours) AS 'Idle time',
FROM
Kronos AS k
LEFT JOIN de_metrics AS de ON k.id=de.kid
LEFT JOIN de_kpi AS dk ON de.decid=dk.cat_id
AND dk.month=month('2005-11-04') AND dk.year=year('2005-11-04')
WHERE
k.date='2005-11-04'
GROUP BY k.aid
ORDER BY
k.training, k.shift, k.id;
PB
-----
Scott Hamm wrote:
>I created a report that shows effiency for each associate (K.AID). I
>am trying to figure out how to use GROUP BY to AVG(Effiency) for each
>K.AID in subquery. Originally I used temporary tables (4) to figure
>out the average effiency for each K.AID. That often left hanging
>temporary tables and took long time to process information. And I also
>need to sum(dhours) on hours worked for each category and to subtract
>that from K.hours (clocked in hours) which shows the idle time for
>each K.AID. I already RTFM and STFW trying to figure out how to use
>GROUP BY to no avail. Here is the detailed information and example of
>my current database that I am working on. Any helpful insights or
>suggestions would be of a big help and appreciated..
>
>Thanks,
>
>
>Scott
>
>--CODE--
>
>mysql> EXPLAIN KRONOS;
>+----------+--------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+----------+--------------+------+-----+---------+----------------+
>| ID | int(10) | NO | PRI | NULL | auto_increment |
>| AID | int(10) | NO | MUL | | |
>| DATE | datetime | NO | | | |
>| HOURS | decimal(4,2) | NO | | | |
>| PTO | decimal(4,2) | YES | | NULL | |
>| Training | tinyint(3) | NO | | | |
>| LocID | int(10) | YES | | NULL | |
>| Shift | int(10) | NO | | | |
>+----------+--------------+------+-----+---------+----------------+
>8 rows in set (0.02 sec)
>
>mysql> EXPLAIN DE_METRICS;
>+--------+--------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+--------+--------------+------+-----+---------+----------------+
>| ID | int(10) | NO | PRI | NULL | auto_increment |
>| KID | int(10) | NO | MUL | | |
>| DECID | int(10) | NO | | | |
>| DQTY | int(10) | NO | | | |
>| DHours | decimal(4,2) | NO | | | |
>+--------+--------------+------+-----+---------+----------------+
>5 rows in set (0.01 sec)
>
>mysql> EXPLAIN DE_KPI;
>+--------------+-------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+--------------+-------------+------+-----+---------+-------+
>| cat_id | int(10) | NO | PRI | | |
>| month | smallint(5) | NO | PRI | | |
>| year | int(10) | NO | PRI | | |
>| goal | int(10) | NO | | | |
>| AdminID | int(10) | NO | | | |
>| updated_date | datetime | YES | | NULL | |
>+--------------+-------------+------+-----+---------+-------+
>6 rows in set (0.01 sec)
>
>mysql> SELECT
> -> k.id,
> -> k.training,
> -> k.shift,
> -> k.aid,
> -> k.hours,
> -> k.pto,
> -> de.decid,
> -> de.dqty,
> -> de.dhours,
> -> CAST((de.dqty/de.dhours) AS DECIMAL(5,0)) AS 'PPH',
> -> dk.goal,
> -> CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)) AS 'Effiency'
> -> FROM
> -> Kronos k
> -> LEFT JOIN
> -> de_metrics DE
> -> ON DE.KID=K.ID
> -> LEFT JOIN
> -> DE_KPI DK
> -> ON DK.cat_id=DE.DECID
> -> AND DK.month=month('2005-11-04')
> -> AND DK.year=year('2005-11-04')
> -> WHERE
> -> K.date='2005-11-04'
> -> ORDER BY
> -> k.training,
> -> k.shift,
> -> k.id;
>+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
>| id | training | shift | aid | hours | pto | decid | dqty | dhours
>| PPH | goal | Effiency |
>+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
>| 2 | 0 | 1 | 73 | 8.08 | 0.00 | 20 | 959 | 6.76
>| 141.86 | 50 | 283.73 |
>| 4 | 0 | 1 | 5179 | 6.98 | 0.00 | 2 | 109 | 3.60
>| 30.28 | 60 | 50.46 |
>| 4 | 0 | 1 | 5179 | 6.98 | 0.00 | 20 | 21 | 0.20
>| 105.00 | 50 | 210.00 |
>| 5 | 0 | 1 | 26 | 7.95 | 0.00 | 19 | 52 | 1.13
>| 46.02 | 46 | 100.04 |
>| 5 | 0 | 1 | 26 | 7.95 | 0.00 | 20 | 441 | 5.28
>| 83.52 | 50 | 167.05 |
>| 5 | 0 | 1 | 26 | 7.95 | 0.00 | 22 | 17 | 0.48
>| 35.42 | 110 | 32.20 |
>| 6 | 0 | 1 | 30 | 9.73 | 0.00 | 10 | 0 | 9.23
>| 0.00 | NULL | NULL |
>| 8 | 0 | 1 | 5080 | 7.25 | 0.00 | 10 | 0 | 5.41
>| 0.00 | NULL | NULL |
>| 9 | 0 | 1 | 4589 | 8.08 | 0.00 | 20 | 644 | 6.85
>| 94.01 | 50 | 188.03 |
>| 10 | 0 | 1 | 4697 | 7.93 | 0.00 | 1 | 129 | 1.28
>| 100.78 | 65 | 155.05 |
>| 10 | 0 | 1 | 4697 | 7.93 | 0.00 | 2 | 160 | 3.39
>| 47.20 | 60 | 78.66 |
>| 11 | 0 | 1 | 4420 | 7.57 | 0.00 | 1 | 40 | 0.65
>| 61.54 | 65 | 94.67 |
>| 11 | 0 | 1 | 4420 | 7.57 | 0.00 | 10 | 0 | 5.46
>| 0.00 | NULL | NULL |
>| 12 | 0 | 1 | 336 | 7.28 | 0.00 | 19 | 19 | 0.35
>| 54.29 | 46 | 118.01 |
>| 12 | 0 | 1 | 336 | 7.28 | 0.00 | 20 | 645 | 5.66
>| 113.96 | 50 | 227.92 |
>| 13 | 0 | 1 | 36 | 8.13 | 0.00 | 20 | 348 | 6.05
>| 57.52 | 50 | 115.04 |
>| 14 | 0 | 1 | 4497 | 7.58 | 0.00 | 19 | 104 | 2.15
>| 48.37 | 46 | 105.16 |
>| 14 | 0 | 1 | 4497 | 7.58 | 0.00 | 20 | 328 | 3.61
>| 90.86 | 50 | 181.72 |
>| 15 | 0 | 1 | 321 | 7.92 | 0.00 | 20 | 371 | 6.13
>| 60.52 | 50 | 121.04 |
>| 16 | 0 | 1 | 484 | 7.33 | 0.00 | 10 | 0 | 5.17
>| 0.00 | NULL | NULL |
>| 16 | 0 | 1 | 484 | 7.33 | 0.00 | 20 | 131 | 1.41
>| 92.91 | 50 | 185.82 |
>| 17 | 0 | 1 | 5605 | 5.48 | 0.00 | 2 | 25 | 0.54
>| 46.30 | 60 | 77.16 |
>| 17 | 0 | 1 | 5605 | 5.48 | 0.00 | 10 | 0 | 0.33
>| 0.00 | NULL | NULL |
>| 17 | 0 | 1 | 5605 | 5.48 | 0.00 | 20 | 334 | 3.15
>| 106.03 | 50 | 212.06 |
>| 18 | 0 | 1 | 119 | 7.40 | 0.00 | 20 | 688 | 6.03
>| 114.10 | 50 | 228.19 |
>| 20 | 0 | 1 | 4381 | 6.83 | 0.00 | 20 | 374 | 5.01
>| 74.65 | 50 | 149.30 |
>| 22 | 0 | 1 | 4382 | 7.32 | 0.00 | 10 | 0 | 6.72
>| 0.00 | NULL | NULL |
>| 3 | 0 | 2 | 721 | 7.92 | 0.00 | 1 | 106 | 1.35
>| 78.52 | 65 | 120.80 |
>| 3 | 0 | 2 | 721 | 7.92 | 0.00 | 2 | 113 | 1.58
>| 71.52 | 60 | 119.20 |
>| 3 | 0 | 2 | 721 | 7.92 | 0.00 | 20 | 79 | 0.71
>| 111.27 | 50 | 222.54 |
>| 7 | 0 | 2 | 2457 | 8.00 | 0.00 | 1 | 150 | 1.34
>| 111.94 | 65 | 172.22 |
>| 7 | 0 | 2 | 2457 | 8.00 | 0.00 | 2 | 178 | 1.63
>| 109.20 | 60 | 182.00 |
>| 7 | 0 | 2 | 2457 | 8.00 | 0.00 | 4 | 28 | 0.30
>| 93.33 | 75 | 124.44 |
>| 7 | 0 | 2 | 2457 | 8.00 | 0.00 | 19 | 12 | 0.12
>| 100.00 | 46 | 217.39 |
>| 19 | 0 | 2 | 4474 | 8.00 | 0.00 | NULL | NULL | NULL
>| NULL | NULL | NULL |
>| 21 | 0 | 2 | 1016 | 8.02 | 0.00 | 10 | 0 | 7.52
>| 0.00 | NULL | NULL |
>| 23 | 0 | 2 | 4561 | 8.65 | 0.00 | 10 | 0 | 8.15
>| 0.00 | NULL | NULL |
>| 1 | 1 | 1 | 6105 | 7.53 | 0.00 | 20 | 754 | 6.25
>| 120.64 | 50 | 241.28 |
>+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
>38 rows in set (0.00 sec)
>
>--END OF CODE--
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/166 - Release Date: 11/10/2005
