Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Traverse previous record and next record

Raj0046Oct 23 2024

Hi Gurus & @mathguy ,

Below is my requirement:

If Employee does not have time entry (clock in clock out) and if employee does not have any leave request on that day(provided employee is not scheduled to work on that day because of his/her shifts or the day being public holiday), then it should be considered as Loss of Pay, along with this if a specific day is Loss of pay, and if previous day and next day is week off or public holiday, then those days should also be considered as Loss of Pay. @mathguy I know you can do miracles in few minutes using match_pattern, but discussed in another post unfortunately my application is not supporting “{” at data base level.

Please find sample table and insert statement:

create table temp2 (DAY_DATE date,PERSON_ID number,CLOCK_IN varchar2(100),CLOCK_OUT varchar2(100),LEAVE varchar2(100),AVAILABILITY varchar2(100),LOP varchar2(10))

insert into temp2 values(to_date('01-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('02-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('03-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('04-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('05-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('06-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('07-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('08-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('09-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('10-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('11-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('12-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('13-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('14-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('15-10-2024','DD-MM-RRRR'),1000,,,'SL','Scheduled',NULL);
insert into temp2 values(to_date('16-10-2024','DD-MM-RRRR'),1000,'09:00','06:00','SL','Scheduled',NULL);
insert into temp2 values(to_date('17-10-2024','DD-MM-RRRR'),1000,'09:00','06:00','SL','Scheduled',NULL);
insert into temp2 values(to_date('18-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('19-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('20-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('21-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('22-10-2024','DD-MM-RRRR'),1000,'09:00','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('23-10-2024','DD-MM-RRRR'),1000,'09:18','06:00',NULL,'Scheduled',NULL);
insert into temp2 values(to_date('24-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('25-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('26-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('27-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);
insert into temp2 values(to_date('28-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('29-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('30-10-2024','DD-MM-RRRR'),1000,,,NULL,'Scheduled','LOP');
insert into temp2 values(to_date('31-10-2024','DD-MM-RRRR'),1000,,,NULL,'Week Off/Public Holiday',NULL);

As you can see from above date Employee has LOP on '25-10-2024' and 26th and 27th are Week Off/Public Holiday, so whenever such pattern exists whether its after next LOP or before the LOP record, those records eg 26th and 27th should also be marked as LOP in the output. Please note that my actual SQL has got inline tables using which finally I am fetching this data. So based on above data, could you please help me to mark previous and next days as LOP if those days are marked as 'Week Off/Public Holiday'.

Regards

Raj

Comments
Post Details
Added on Oct 23 2024
3 comments
119 views