Thursday, November 12, 2009

Minimum and Maximum Rate by Timekeeper Title

This fairly simple script will find the current minimum and maximum rate per title for non-terminated timekeepers whose time entry flag is checked. This is based on rates in the timerate table versus the accounting rate table.

SELECT tktitle AS 'Title',
MIN(tkrt01) AS 'Minimum Rate',
MAX(tkrt01) AS 'Maximum Rate'
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
GROUP BY tktitle

No comments:

Post a Comment