The LEAD and LAG aggregate functions and what to do when you don’t have them

I recently worked with an insurance company on an interesting issue involving dates. They had a series of records that all had a start date but no end (or cancel) date. The end date of a record was implicitly recognized as the start date of the next highest record.

For example, a table called IMPORTANT_THINGS:


The first record is effective for customer number 1 beginning on January 1, 2017 and ending on January 1, 2018. We have a frequent need to represent that on a single row of a result set. The goal is to produce the following result:


To find out how, check out the full article below.

Recent Stories
Early stage predicate evaluation with DECFLOAT and implicit casts

Meet “the Crusher” - or how we learned to love query acceleration

May sum up at IDUG