February 9, 2011 at 6:40 am
I know this is a classic problem. Forgive my "senior moment". If you can point me to resources, I'd appreciate it.
I have a table of workers, a table of vendors (employers), a table of labor rates (job titles within vendor), and a transaction table (half a million timesheet entries). The application is a timesheet entry app.
Workers/Vendors are related in a many-to-many relationship, where there is exactly one valid set (active dates)
As the row source for a combo box in Access 2007, I need to find the labor rate most recently used by a given worker. The code I'm using right now is wrong. It finds the highest labor rate ID instead. It also uses Access' domain aggregate function, which (on a half-million row tabl) is remarkably slow....
I can't seem to get my head around the correct SQL. Can you help me out here?
SELECT xrefWorkerVendor.WorkerID, xrefWorkerVendor.VendorID, tblWorkers.WorkerName, xrefWorkerVendor.ExpiryDate, DMax("LaborRateID","tblStaffAugTrans","WorkerID=" & [WorkerID]) AS LaborID
FROM tblWorkers INNER JOIN xrefWorkerVendor ON tblWorkers.ID = xrefWorkerVendor.WorkerID;
Jim
February 9, 2011 at 7:58 am
Jim,
without the table layouts and sample data, it's hard to give you code that will work. With that said, it looks like you are getting the Max(LaborRateID) instead of the 'labor rate most recently used'. I think you need to find the Max(ActiveDate) for each employee and then use that to get the LaborRateID.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 8:08 am
Sorry, the tables and such are pretty complex. I think this is a classic problem. I need the labor rate ID column from the transaction table row with the most recent date. The other stuff is less important.
I think it's a subquery. Let's ignore the other tables and just find the labor rate most recently used in the transaction table for a given WorkerID.
I can select the max date, but can't figure out how to select the labor rate that goes with the max date....
Jim
February 9, 2011 at 8:26 am
Try something like this:
declare @transaction table (WorkerID tinyint, TransactionDate smalldatetime, laborRate decimal (6,2))
insert into @transaction
select 1, '2011-01-03', 100 union all
select 1, '2011-01-04', 105 union all
select 1, '2011-01-05', 95 union all
select 2, '2011-01-09', 100 union all
select 2, '2011-01-10', 110 union all
select 3, '2011-01-10', 30
select t.WorkerID, t.TransactionDate, t.laborRate
from @transaction t
inner join (select WorkerID, max(Transactiondate) TransactionDate
from @transaction
group by WorkerID) t1
on t.WorkerID = t1.WorkerID
and t.TransactionDate = t1.TransactionDate
order by t.WorkerID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 8:58 am
That would be great, except a worker can have multiple timesheet entries (with possibly different Labor Rates) on the same day. In fact, the whole labor rate thing revolves around workers being able to take on multiple roles.
I think I got it by taking advantage of the row ID (autonumbered...)
SELECT T.WorkerID, T.LaborRateID
FROM tblStaffAugTrans AS T
WHERE T.ID =
(SELECT TOP 1 H.ID
FROM tblStaffAugTrans as H
WHERE T.WorkerID=H.WorkerID)
ORDER BY T.WorkerID;
This does run a bit slow. 4 seconds to return. Which index(es) should I address to be sure I'm optimizing this query? ID is, of course, indexed. As a foreign key, I'm pretty sure WorkerID is also indexed. Or is there some other optimization I need to think about?
Jim
February 9, 2011 at 9:08 am
The answer is it depends. I don't have your table layouts or sample data, so I don't know what the specifics are. What you can do is take the SQL in SSMS and right-click on th equery and select to analyze it in the Database Engine Tuning Advisor. That will give you some recommendations for indexes, stats, etc..
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply