Find row in related table with closest date

Question

I need to find service.service_id, service.name and service.date_begin for each row in bonus table with closest service.date_begin and service.date_begin <= bonus.date_begin. If there are more than one service with such date_begin, return any service (e.g. with max service.service_id or service.rowid). If there are no such service, return NULL.

Example

bonus table (bonus_id is PK):

bonus_id  date_begin
--------------------
1         2010-04-12
2         2010-04-20

service table (service_id is PK):

bonus_id  service_id  name  date_begin
--------------------------------------
1         1           'a'   2010-04-10
1         2           'b'   2010-04-11
1         3           'c'   2010-04-11
1         4           'd'   2010-04-15
2         5           'e'   2010-04-22

Desired output:

bonus_id  bonus_date_begin  service_id  service_name  service_date_begin
------------------------------------------------------------------------
1         2010-04-12        3           'c'           2010-04-11
2         2010-04-20        NULL        NULL          NULL

Database: Oracle 11.2

Population script:

create table bonus (
  bonus_id number primary key,
  date_begin date
);
create table service (
  bonus_id number references bonus(bonus_id),
  service_id number primary key,
  name varchar2(1),
  date_begin date
);
insert into bonus values (1, date '2010-04-12');
insert into bonus values (2, date '2010-04-20');
insert into service values (1, 1, 'a', date '2010-04-10');
insert into service values (1, 2, 'b', date '2010-04-11');
insert into service values (1, 3, 'c', date '2010-04-11');
insert into service values (1, 4, 'd', date '2010-04-15');
insert into service values (2, 5, 'e', date '2010-04-22');
commit;

Answer 1

SELECT b.bonus_id,
       MAX( b.date_begin ) AS bonus_date_begin,
       MAX( s.service_id ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_id,
       MAX( s.name       ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_name,
       MAX( s.date_begin ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_date_begin
FROM   bonus b
       LEFT OUTER JOIN
       service s
       ON ( b.bonus_id = s.bonus_id AND s.date_begin < b.date_begin )
GROUP BY b.bonus_id;

Output:

BONUS_ID BONUS_DATE_BEGIN SERVICE_ID SERVICE_NAME SERVICE_DATE_BEGIN
-------- ---------------- ---------- ------------ ------------------
1        2010-04-12       3          c            2010-04-11
2        2010-04-20       NULL       NULL         NULL