Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table employee (
- employee_id bigint not null primary key,
- first_name varchar(50) not null,
- middle_name varchar(50) null,
- last_name varchar(50) not null
- )
- create table eligibility (
- eligibility_id bigint not null primary key,
- employee_id bigint not null foreign key references employee (employee_id),
- effective_date date not null,
- sequence_number int not null,
- value varchar(20) not null,
- constraint UK_eligibility unique (employee_id, effective_date, sequence_number)
- )
- insert into employee (employee_id, first_name, middle_name, last_name) values (1001, 'A', 'B', 'C')
- insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (1, 1001, '2016-04-13', 1, 'NS')
- insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (2, 1001, '2016-05-25', 1, 'EX')
- insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (3, 1001, '2016-05-25', 2, 'VR')
- insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (4, 1001, '2016-06-05', 1, 'LS')
- select * from eligibility e
- where
- e.effective_date = (select max(effective_date)
- from eligibility e1
- where e1.employee_id = e.employee_id and
- e1.effective_date <= '2016-05-30') AND
- e.sequence_number = (select max(sequence_number)
- from eligibility e2
- where e2.employee_id = e.employee_id and
- e2.effective_date = e.effective_date)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement