StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As Jacob offered an option, I too would make some changes to normalization too.</p> <pre><code>Table PayCodes PayCodeID PayCodeDescription timesBase timesOtherRate timesMealRate 1 01 Ordinary 1.0 0.0 0.0 2 02 Overtime 1.5 0.0 0.0 3 03 Overtime 2.0 0.0 0.0 4 78 Crib .333 0.0 0.0 5 CZ Meal Allowance PS 0.0 0.0 1.0 6 86Y Sick with Cert 1.0 0.0 0.0 etc... </code></pre> <p>Then, in your hourly table which data entry would be data driven to get the corresponding paycodes, save the internal ID Code vs the hard-code word references. You could even build another column for a "PayStubCaption" if you didn't want to show any cryptic references.</p> <p>For your employee pay rate history, have the fields include begin/end dates since it is a rare occasion (once a year?? MAYBE twice??) Have both begin and end date of the given rate and also include (in this example the 3 rates you track..</p> <pre><code>PayHistoryID EmployeeID FromDate ToDate BaseRate OtherRate MealRate 1 84238 2012-10-10 2013-9-30 10.0 15.0 3.5 2 84238 2013-10-1 (current) 14.0 21.0 6 ... </code></pre> <p>These can significantly help simplify querying downstream...</p> <p>As for adding to the query for the per rate to be applied, that will become a simple join provided you dont allow bad data, such as a from/to range on one record crosses that of another for the same person...</p> <pre><code>select ... sum( case/when... PRT.BaseRate ) as ... from other join tables... JOIN PayRatesTable PRT on employeehours_copy.EmployeeID PRT.EmployeeID AND PRT.FromDate <= employeehours_copy.Worked AND ( PRT.ToDate IS NULL OR employeehours_copy.Worked <= PRT.ToDate ) </code></pre> <p>Notice for simplified reading, I've aliased the sample "PayRateTable" to PRT. Then just use the "PRT.BaseRate" or other rate, meals rate for each computation instead of the rate that is possibly in your employee table. You should also get in the habit of qualifying all your table.column (or alias.column) for ambiguity problems, especially if ever a join where the column names are the same in each table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload