1

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;
avd
  • 318
  • 1
  • 7

3 Answers3

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
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks! But 1) `s.date_begin <= b.date_begin` 2) `b.bonus_id` is unique, so no need for `group by` and `max(b.date_begin)`. – avd May 02 '17 at 08:59
  • @sltn It is required as there are multiple rows being joined from the `service` table for each row in the `bonus` table. – MT0 May 02 '17 at 09:00
  • OK, I understood. Just curious, why do you prefer `max(b.date_begin)` over `group by b.bonus_id, b.date_begin`? – avd May 02 '17 at 09:06
  • `MAX( b.date_begin )` will only return one row per `bonus_id` where as `GROUP BY b.bonus_id, b.date_begin` will return one row per distinct row in the `bonus` table. If there is only a single row in the `bonus` table per `bonus_id` then they are the same but that was not specified in your question. – MT0 May 02 '17 at 09:17
  • I added to the question that `bonus_id` and `service_id` are PKs. Thanks for the answer! – avd May 02 '17 at 09:29
0

Try this code, this will give your desired output.

select bonus.bonus_id ,bonus.date_begin as bonus_date_begin,max(service.service_id) as service_id,
max(service.date_begin) as service_date_begin 
from bonus left join service
on bonus.bonus_id=service.bonus_id
and service.date_begin <= bonus.date_begin
group by bonus.bonus_id ,bonus.date_begin
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • This only works when `service.date_begin` and `service.service_id` both increment in the same order. If the data is such that `service.service_id` is in decreasing order (or random order) then the `service_id` will likely not be from the same row as the `date_begin`. – MT0 May 02 '17 at 08:33
0
select
  t.bonus_id,
  t.bonus_date_begin,
  t.service_id,
  t.service_name,
  t.service_date_begin
from (
  select
    b.bonus_id,
    b.date_begin as bonus_date_begin,
    s.service_id,
    s.name as service_name,
    s.date_begin as service_date_begin,
    row_number() over (
      partition by b.bonus_id
      order by s.date_begin desc, s.service_id desc
    ) as rn
  from avd_bonus b
  left join avd_service s on b.bonus_id = s.bonus_id and s.date_begin <= b.date_begin
) t
where t.rn = 1;
avd
  • 318
  • 1
  • 7