In a project I am working on, I have several tables that contain values with "effective dates"

For example a table with pay rates can have several pay rates for the same position, as the pay rate changes from time to time.

I am trying to structure a query that returns all of the records from a timesheet table and the corresponding pay rate for the job code (the job code is the link between the timesheet table and the pay rates table)

The problem I am having is that the pay rate may change say twice in a year, so I have to return all records subsequent to the change pay rate date with the most recent pay rate.

I hope this is making sense, because I am getting a little tired of this issue at the moment.

Here is the sub query I have come up with so far, unfortunately it does not work,. hahaha

As you will see, I created a view of the pay rates table sorting the records in descending order by effective date. In a single view, this works great, but on a sub query it gets a little wonky

ANY suggestions you could offer would be greatly appreciated.


SELECT TOP 100 PERCENT ts.ShiftDate, ts.idJobCode, COUNT(ts.eeLink) AS Points
FROM dbo.tbl_ProcTimesheet ts INNER JOIN
dbo.GratBanqPointsByDate ON ts.idJobCode = dbo.GratBanqPointsByDate.idJobCode
WHERE (ts.Sequence = 0) AND (ts.ShiftDate BETWEEN CONVERT(DATETIME, '2005-11-27 00:00:00', 102) AND CONVERT(DATETIME, '2005-12-07 00:00:00',
102))
GROUP BY ts.ShiftDate, ts.idJobCode
HAVING (ts.ShiftDate >=
(SELECT TOP 1 (EffectiveDate)
FROM dbo.GratBanqPointsByDate
WHERE idJobCode = ts.idJobCode))
ORDER BY ts.ShiftDate, ts.idJobCode

any suggestions? sure

first of all, ditch "top 100 percent" -- that always strikes me as silly

second, you don't need to explicitly CONVERT datetime strings to datetime, but if you're gonna do it, pick the right style code (you want 120, not 102)

your correlated subquery is the right idea, but it should be applied in an ON condition as part of the join, and it should be applied to the job code table

furthermore, i think there should be a correlation of pay code date to timesheet shift date

the use of "greater than or equal to the TOP 1 ORDER BY" should simply be "equal to the MAX"

well, that was a lot of changes, so i'm not 100% sure i got everything right ...