One of the neat things about this query is the method used to "create" an ending effective date for each record in the rate table. By default, this table only has a beginning effective date. Therefore, by joining the table to itself we are able to match the next highest effective date for a record and subtract a day from it to arrive at an ending date for that record.
Note: While this query is correctly configured to take multi-currency into account, it is assuming that everyone has a US Dollar rate. If that's not the case for your firm, modifications will need to be made. Also, for this example, Rate Code 2 is considered the "standard" or "rack" rate.
WITH rates AS ( SELECT b.tkinit AS tkrinit ,b.tkeffdate AS tkrdate1 ,MIN(CASE WHEN e.tkeffdate IS NULL THEN '1/1/2026' ELSE e.tkeffdate END)-1 AS tkrdate2 ,b.tkrt02 ,b.tkrtcur FROM timerate b LEFT OUTER JOIN timerate e ON b.tkinit=e.tkinit AND e.tkeffdate > b.tkeffdate AND b.tkrtcur=e.tkrtcur GROUP BY b.tkinit,b.tkeffdate,b.tkrt02,b.tkrtcur ) SELECT head1 AS 'Department' ,tkinit AS 'TK #' ,tklast+', '+tkfirst AS 'Timekeeper' ,SUM(tworkhrs) AS 'Worked Hrs' ,CAST(SUM(CASE WHEN rates.tkrt02 iS NULL THEN tworkhrs*dorates.tkrt02 ELSE tworkhrs*rates.tkrt02/cdrate END) AS DECIMAL(16,2)) AS 'R2 Fees' ,CAST(SUM(tworkdol/cdrate) AS DECIMAL(16,2)) AS 'Worked Fees' ,CAST(SUM(tbilldol/cdrate) AS DECIMAL(16,2)) AS 'Billed Fees' FROM timecard INNER JOIN timekeep ON ttk=tkinit INNER JOIN deptlab ON tkdept=delcode INNER JOIN periodt ON tbiper=pe INNER JOIN matter ON tmatter=mmatter INNER JOIN currates ON mcurrency=curcode AND pebedt BETWEEN cddate1 AND cddate2 AND trtype='A' LEFT OUTER JOIN rates ON rates.tkrinit=tkinit AND tworkdt BETWEEN rates.tkrdate1 AND rates.tkrdate2 AND mcurrency=rates.tkrtcur LEFT OUTER JOIN rates dorates ON dorates.tkrinit=tkinit AND tworkdt BETWEEN dorates.tkrdate1 AND dorates.tkrdate2 AND dorates.tkrtcur='DO' WHERE YEAR(pebedt)='2010' AND pebedt < '12/1/10' AND tstatus NOT IN ('E','NBP') GROUP BY tkdept,head1,tkinit,tklast,tkfirst ORDER BY tkdept,head1,tklastThe total results for Worked Hours, Worked Fees and Billed Fees should tie back to Timekeeper and Matter Stats in Inquiry.
No comments:
Post a Comment