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?

score 0 · Answer 1 · answered Aug 29 '12 at 18:47

0

Try this :)

SELECT eb.submitted_date
FROM event_bill eb
WHERE
  eb.event_id IN (
    SELECT event.event_id 
    FROM event 
    WHERE 
      event.customer_id = @custID 
      AND event.event_type = 'Followup'
      AND event.event_id < @eventID
    ORDER BY event_date DESC
     LIMIT 1
   )

answered Aug 29 '12 at 18:47

Parallelis

699
3
6

It's sybase-ase, there is no option 'limit 1' – Robert Aug 29 '12 at 18:50

score 0 · Accepted Answer · answered Aug 29 '12 at 18:47

0

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

answered Aug 29 '12 at 18:47

Robert

25,425
8
67
81

Thanks so much for your help! Now that I see this written out, it makes more sense that I just had to rejoin the event table on itself again. – Jennifer S Aug 29 '12 at 19:06

Gordon Linoff · Answer 3 · 2012-08-29T19:02:16.983

0

I would think that the ORDER BY would be accepted with the top. If not, you can do this:

 SELECT eb.submitted_date
 FROM event_bill eb JOIN
      event e
      on eb.event_id = e.event_id join
      (SELECT customer_id, MAX(eventdate) as maxdate
       FROM  event
       WHERE customer_id = @custID AND
            event_type = 'Followup' AND
            event_id < @eventID
       group by customer_id
      ) md
     ON e.customer_id = md.customer_id and
        eb.event_date = md.maxdate

This calculates the maxdate and then uses this for the join.

edited Aug 29 '12 at 19:02

answered Aug 29 '12 at 18:48

Gordon Linoff

1,242,037
58
646
786

The problem with this one is that the customer ID and event_date are only in event, and event_bill only has event_id and submitted_date, which is different than event_date. – Jennifer S Aug 29 '12 at 18:59

SQL Top and Join

3 Answers3