Search code examples
sqloracle-databasegaps-and-islands

Oracle Function to extract first / last value of code that with multiple series

I'm having difficulties finding a way to extract the first and last values for a code in a series that can occur multiple times in a day. Example, table data agents schedule can have breaks, lunch and other codes such as coach and ect. I need to pull the first and last value for each coaching session for each agent per day. I've attached pic of table data and expected output.

When I use last and first value functions, it wants to group all of the coaching as one instead of two for the example. This is where I'm having the issue.

PIC example:

enter image description here

Solution

  • Try this (START as column name will not work, I changed to STRT)

    SELECT * FROM DATA
MATCH_RECOGNIZE (
PARTITION BY id, code
ORDER BY strt, stop
MEASURES first(strt) AS strt, MAX(stop) AS stop
PATTERN( merged* a )
DEFINE
  merged AS stop = NEXT(strt)
)
WHERE code= 'Co'
;