Wednesday, September 9, 2009

Maximum and Minimum Rate per Timekeeper Title

This query will return by timekeeper title the highest and lowest current rate found in the timerate table. This doesn't take into account the accounting rate table.
SELECT tktitle, MIN(tkrt01) AS minrate, MAX(tkrt01) AS maxrate
FROM timekeep
  SELECT tkinit, MAX(tkeffdate) AS tkeffdate
  FROM timerate
  GROUP BY tkinit) AS recentrate ON recentrate.tkinit=timekeep.tkinit
INNER JOIN timerate ON recentrate.tkinit=timerate.tkinit AND recentrate.tkeffdate=timerate.tkeffdate
WHERE tkeflag = 'Y'
AND tktmdate IS NULL
AND tksect = 'Team01'
GROUP BY tktitle

No comments:

Post a Comment