Search code examples
sqlms-access-2007

How to check newer than or equal to a date on access SQL

Currently I have two tables, using Access 2007

TimeSheet(empID, TimeSheet, hours) and Rates(empID,Rate,PromotionDate)

How do I select the correct billing rates of employee based on their date of promotion? For example, I have 

Rates(001,10,#01/01/2013#)

Rates(001,15,#01/05/2013#)

Rates(002,10,#01/01/2013#)

and

Timesheet(001,#02/01/2013#,5)

Timesheet(001,#02/05/2013#,5)

Timesheet(002,#02/01/2013#,7)

In this case, I want to show that if empID 001 submited a time sheet at 02/01/2013 it would be billed with $10/hr , but his timesheets starting at May 1st would be billed with $15/hr

My query right now is

SELECT t.empID , t.timesheet, r.hours , 
    (SELECT rate FROM rate WHERE t.timeSheet >= r.promotionDate) AS RateBilled 
FROM rate AS r , timesheet AS t
WHERE r.empid = t.empid

When ran, it shows a message of “At most one record can be returned by this subquery”

Any help would be appreciated, thanks.

Edit:

I have some strange output using the sql 

SELECT t.empID, t.timesheet, r.rate AS RateBilled
FROM Rates AS r, timesheet AS t
WHERE r.empid=t.empid And t.timeSheet>=r.promotionDate
GROUP BY t.empID, t.timesheet, r.rate, r.promotionDate
HAVING r.promotionDate=MAX(r.promotionDate);

results

as you can see the output table ratebilled for empID 1 is switching back and forth from 10 to 15, even though past May 01, it should all be using 15 ,

any help is appreciated, thanks.

Solution

  • The select subquery you have setup potentially returns multiple values where only one should be returned. Consider the case where there may be two promotions and a recent timesheet, then the select will return two values because on both occasions the timesheet is newer than the promotion.

    Try using the following as your subquery:

    SELECT TOP 1 rate FROM rate 
WHERE t.timeSheet >= r.promotionDate
ORDER BY r.promotionDate DESC

    N.B. I don't think the above is terribly efficient. Instead try something like

    SELECT t.empID , t.timesheet, r.hours , r.rate AS RateBilled
FROM rate AS r , timesheet AS t
WHERE r.empid = t.empid AND t.timeSheet >= r.promotionDate
GROUP BY t.empID, t.timesheet
HAVING r.promotionDate = MAX( r.promotionDate);