SQL Find Nearest Effective Date in Related Table

Question

I'll preface this by stating that this problem is similar to SQL Join on Nearest Less Than Date but that the solution there doesn't work for my problem. Instead of selecting a single column, I need the results of a table that is 'filtered' based on the nearest date.

I have three tables. The main table contains time ticket data in the form:

ticketId 
ticketNumber 
ticketDate
projectId

A secondary table tracks rate schedules for resources on each daily ticket for the project. It looks something like this:

scheduleId
projectId
effectiveDate

There is also a third table that is related to the second that actually contains the applicable rates. Something like this:

scheduleId
straightTime
overTime

Joining the first two tables on projectId (obviously) replicates data for every record in the rate schedule for the project. If I have 3 rate schedules for project 1, then ticket records result in something like:

 ticketNumber  | ticketDate   | projectId   | effectiveDate  | scheduleId
 ------------- | ------------ | ----------- | -------------- | ----------
 1234          | 2016-06-18   | 25          | 2016-06-01     | 1
 1234          | 2016-06-18   | 25          | 2016-06-15     | 2
 1234          | 2016-06-18   | 25          | 2016-06-31     | 3

Selecting the effectiveDate into my results is straightforward with the example:

  SELECT *
    ,  (SELECT TOP 1 t1.effectiveFrom 
        FROM         dbo.labourRateSchedule t1
        WHERE        t1.effectiveFrom <= t2.[date] and t1.projectId = t2.projectId
        ORDER BY     t1.effectiveFrom desc) as effectiveDate

  FROM dbo.timeTicket t2
  ORDER BY t.date

However, I need to be able to join the ID of dbo.labourRateSchedule onto the third table to get the actual rates that apply. Adding the t1.ID to the SELECT statement does not make it accessible to JOIN into another related table.

I've been trying to JOIN the SELECT statement in the FROM statement but the results are only resulting with the last effectiveDate value instead of one that is closest to the applicable ticketDate.

I would hugely appreciate any help on this!

To clear things a bit please provide table names instead of "first, second" so the query can be compared to the structures. — Serg, Sep 15 '17 at 06:59

score 2 · Accepted Answer · answered Sep 15 '17 at 07:20

You can move your subquery to the FROM clause by using CROSS APPLY:

SELECT *
FROM dbo.timeTicket tt
CROSS APPLY
(
  SELECT TOP(1) *
  FROM dbo.labourRateSchedule lrs
  WHERE lrs.projectId = tt.projectId
    AND lrs.effectiveFrom <= tt.date
  ORDER BY lrs.effectiveFrom desc
) best_lrs
JOIN dbo.schedule s on s.schedule_id = best_lrs.schedule_id
ORDER BY tt.date

score 0 · Answer 2 · answered Sep 15 '17 at 06:53

Can you try something like this (you should change something, as you didn't post all information).

 SELECT A.*, C.*
  FROM timeTicket A
  INNER JOIN  (SELECT * , ROW_NUMBER() OVER (PARTITION BY projectId ORDER BY effectiveFrom DESC) AS RN
                FROM labourRateSchedule) B ON A.projectId=B.projectId AND B.RN=1
  INNER JOIN YOUR3TABLE C ON B.SCHEDULEID=C.SCHEDULEID

score 0 · Answer 3 · answered Sep 15 '17 at 07:05

You can do this by CTE and Rank function -

create table timeTicket  (ticketId  int,
ticketNumber int ,
ticketDate smalldatetime ,
projectId int )
go

create table labourRateSchedule 
(scheduleId int,
projectId int,
effectiveDate smalldatetime ) 
go

create table ApplicableRates
(scheduleId int,
straightTime smalldatetime ,
overTime smalldatetime) 
go

insert into timeTicket
select 1 , 1234   ,'2016-06-18' ,25   
go

insert into labourRateSchedule
select 1 , 25   ,'2016-06-01'   
union all select 2 , 25   ,'2016-06-15'  
union all select 3 , 25   ,'2016-06-30'  
go

insert into ApplicableRates    
select 1 , '2016-06-07'    ,'2016-06-07'   
union all select 2 ,  '2016-06-17'      ,'2016-06-17'  
union all select 3 ,   '2016-06-22'     ,'2016-06-25'           
go

  with cte 
  as (
  select t1.ticketNumber    ,t1.ticketDate  ,t1.projectId       ,t2.effectiveDate   ,t3.scheduleId  ,t3.straightTime    
  ,t3.overTime , rank() over (  partition by t1.ticketNumber order by abs     (DATEDIFF(day,t1.ticketDate, t2.effectiveDate) ) )  DyaDiff 
  from  timeTicket t1 join  labourRateSchedule t2
  on t1.projectId = t2.projectId
  join ApplicableRates t3
  on t2.scheduleId = t3.scheduleId)
  select * from cte where DyaDiff = 1

SQL Find Nearest Effective Date in Related Table

3 Answers3