On 11/10/05, Peter Brawley <peter.brawley@stripped> wrote:
> 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
>
>
Mine was so far:
DECLARE @sdate smalldatetime
SET @sdate='2005-11-04'
SELECT
K.shift,
A.LastName + ', ' + A.FirstName AS 'Name',
K.Hours AS 'Krono',
K.PTO AS 'PTO',
DE.DECID AS 'CatID',
DC.Category AS 'Category',
DE.DQTY AS 'QTY',
DE.DHours AS 'Hours',
DK.goal AS 'Goal',
CAST((de.dqty/de.dhours) AS DECIMAL(5,0)) AS 'PPH',
CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)) AS 'Effiency',
P.AVGEFF AS 'Avg_Eff',
K.hours-P.TOTHRS AS 'Time_rem'
FROM
(
SELECT
DE.KID,
sum(DE.DHours) AS TOTHRS,
CAST(AVG((de.dqty/(dk.goal*de.dhours))*100) AS DECIMAL(5,0)) AS 'AVGEFF'
FROM
DE_Metrics DE
LEFT JOIN
DE_KPI DK
ON DK.cat_id=DE.DECID
AND DK.month=month(@sdate)
AND DK.year=year(@sdate)
WHERE
DE.KID IN
(
SELECT
K.ID
FROM
Kronos K
WHERE
date=@sdate
)
GROUP BY
DE.KID
)AS P
LEFT JOIN
Kronos K ON K.ID=P.KID
LEFT JOIN
Associates A ON A.ID=K.AID
LEFT JOIN
DE_Metrics DE ON DE.KID=K.ID
LEFT JOIN
DECategories DC ON DC.ID=DE.DECID
LEFT JOIN
DE_KPI DK
ON DK.cat_id=DE.DECID
AND DK.month=month(@sdate)
AND DK.year=year(@sdate);
I will try yours and see if that what I was looking for.
Thanks.
