SQL Top and Join

Question

Business Rule: We can only bill for followup events every 90 days. Any events that occur less than 90 days after the previous one cannot be billed, but they need to be recorded.

User requirement: They want to see the date the last event bill was submitted on the tab where they would submit the bill for the current event, to have a visual cue as to whether submitting a bill is worth doing.

Events have an event_id and an event_date in table event. Event_id is a foreign key in table event_bill, which has the submitted_date for the bill. Events have a foreign key customer_id, for each customer, so a customer can have multiple events in any time period.

Given the current event_id and the customer_id, I'm trying to get the submitted_date for the most recent previous event.

Here's what I've tried:

    SELECT TOP 1 (event_id) as prev_event_id
    INTO #tmp
    FROM  event 
    WHERE customer_id = @custID
    AND event_type = 'Followup'
    AND event_id < @eventID 
    ORDER BY event_date DESC

    SELECT eb.submitted_date
    FROM event_bill eb
    JOIN #tmp
    ON eb.event_id = #tmp.prev_event_id

    DROP TABLE #tmp

This would be all well and good, but my application's database permissions don't allow for the creation of the temp table.

In an attempt without the temp table, I got errors that I can't use the ORDER BY in a derived table, but I need that to make sure I get the last event before the current one for this customer:

    SELECT eb.submitted_date
    FROM event_bill eb
    JOIN
    (
    SELECT TOP 1 (event_id) as prev_event_id
    FROM  event 
    WHERE customer_id = @custID
    AND event_type = 'Followup'
    AND event_id < @eventID 
    ORDER BY event_date DESC
    ) x
    ON eb.event_id = x.prev_event_id

Could anyone give me a better way to approach this?

Answer 1

Maybe it will help you

 SELECT eb.submitted_date
    FROM event_bill eb
    JOIN
    (
    SELECT event_id as prev_event_id
    FROM  event 
    WHERE customer_id = @custID
    AND event_type = 'Followup'
    AND event_id < @eventID 
    and event_date = 
    (
      select max(event_date)
      FROM  event 
      WHERE customer_id = @custID
      AND event_type = 'Followup'
      AND event_id < @eventID 
    )
    ) x
    ON eb.event_id = x.prev_event_id