user2170742 user2170742 - 1 month ago 12
SQL Question

Query for effective date based on related table values

I'm having a really hard time trying to figure out a query that targets records in a table that is related to the table where the effectiveDates are stored.

Let's say I have these three tables:

tblProjects
-----------
id

tblTickets
----------
id
projectID
ticketNumber
ticketDate

tblRates
--------
id
projectID
effectiveDate
payRate


What I'm trying to do is create a query that tests the effectiveDate of the Rates table against the ticketDate and finds the latest effectiveDate that is <= ticketDate. Finding the latest is important as it's possible for there to be several different Rate values that apply to the project.

So tblTickets might have:

id      projectId        ticketDate ticketNumber
-----------------------------------------------------------
1       1 2016/08/11 990
2 1 2016/09/12 991
3 1 2016/10/22 992
4 1 2016/10/23 993
5 2 2016/08/15 750
6 2 2016/09/08 751


and tblRates might have:

id projectId effectiveDate Rate
-----------------------------------------------------------
1 1 2016/08/01 $50
2 1 2016/10/01 $75
3 2 2016/06/01 $65


If I were to query for all tickets with the projectId = 1, I would want to see something like this:

projectId ticketNumber TicketDate Rate
------------------------------------------------------------------------
1 990 2016/08/11 $50
1 991 2016/09/12 $50
1 992 2016/10/12 $75
1 993 2016/10/23 $75


I'm not even really sure where to start. I could query for the MAX of the effectiveDate but that will only return the 10/12 and 10/23 records in the example above. I could also simply query for everything that is <= the TicketDate but that would return duplicates as both 08/01 and 10/01 fall under that criteria.

A nudge in the right direction would be greatly appreciated!

Edit:
Here's my current query. It's returning duplicates of all records for each of the Rates.

SELECT
      vRates.projectId
, tblTickets.TicketNumber
, tblTickets.TicketDate
, vRates.Rate
, vRates.effectiveDate

FROM (
 ( SELECT
  vTempRates.effectiveFrom
, vTempRates.projectId
, vRates.Rate
FROM tblRates AS vTempRates
  INNER JOIN
( SELECT
  projectId
, MAX(effectiveFrom) AS effectiveDate
, Rate
FROM tblRates
GROUP BY projectId, Rate
) AS vRates
  ON vTempRates.projectId = vRates.projectId
AND vTempRates.effectiveFrom = vRates.effectiveDate

 ) AS vRates
 INNER JOIN tblProjects
ON vRates.projectId = tblProjects.id)
 INNER JOIN tblTickets
ON tblProjects.id = tblTickets.projectId
WHERE (((tblProjects.id)=1));

Answer

Access 2013

select      t.*
           ,(select top 1 r.Rate from tblRates r where r.projectid = t.projectid and r.effectiveDate <= t.ticketDate order by r.effectiveDate desc) as Rate
from        tblTickets      t   
where       t.projectid = 1
;
Comments