OK, Naomi, you get to go first. :)

Here's a little exercise with some temporal data.

Suppose a company stores pay rates by employee, in a pay rate table. The structure might look like this…

CREATE TABLE WorkerRates (RateID int IDENTITY, EmployeeID int, RateEffectiveDate DateTime, 
                          RateAddedDate DateTime, HourlyRate Decimal(14,2))

Note that there’s both a rate effective date, as well as the date that the rate was added to the database. Presumably, any insertions would contain a rateEffectiveDate that’s either the same as the “current” date, or a future date. So possible entries might be…


-- Employee hired on 12/22, will start on 1-1 with rate of 20.00
INSERT INTO WorkerRates VALUES (1,'1-1-2008','12-22-2007',20.00)

-- Employee gets a three month review on 3-29, gets a raise to 25.00, effective 4-1
INSERT INTO WorkerRates VALUES (1,'4-1-2008','3-24-2008', 25.00)



Now, you can probably see where this is going – there’s going to be a timesheet table, with dates and hours worked.

-- Now create some timesheet records
CREATE TABLE TimeSheets (TimeSheetID int IDENTITY, EmployeeID int, WorkDate DateTime, HoursWorked decimal(10,2))

And so, in a normalized database – when determining labor $$$, you’d need to grab the correct rate for a given day. Not too tough, eh? Well, there’s more. ?

A minute ago, I said “presumably” any insertions would contain a rateEffectiveDate of either “today” or a future date. Well, not so fast….here’s something else to throw into the mix. Suppose a pay rate was supposed to go into effect on a certain day, but didn’t get added to the database until later (maybe the day after payroll was run, when it was discovered that the new rate hadn’t been keyed in)

For example…

-- Employee got a raise to 30.00, it was supposed to be effective 11-1, but wasn't added
-- until 11-16
INSERT INTO WorkerRates VALUES (1,'11-1-2008','11-16-2008', 30.00)

So, any query that joins WorkerRates rows with TimeSheet rows would not only need to grab the correct rate…but suppose six months later, you needed to essentially run the same query, to determine what rate the employee was paid out on that date?

Obviously, the company would need to make some kind of adjustment after an 11/15 payroll….but the company will always need some kind of reporting mechanism to always indicate that the employee was paid out at the “old” rate for the first part of November.

Of course, some developers will take the route of breaking normal form, and will store the pay rate for the day on each timesheet row, at the time the timesheet row is inserted.

But suppose you’re working in an environment where the structure above will be used, and they won't permit storing the valid rate on each timesheet record – and they insist that SQL code must be written to determine the rate that was used on any given day. So you need to write a query that will produce the following result set structure….

EmployeeID   MonthPay     YearPay    LaborDollars
1                          1                        2008          1,000                    (employee 1, January 2008)

…..and guarantee that even if rates are entered “after the fact”, you’ll still get the same results.

So…what would the SQL look like?

The script to create the data is here...
http://www.commongroundsolutions.net/SampleData.rar (it's a small zip that unzips to a script)