PL/SQL for some extra juice

  2019-02-26


I write a lot of SQL in my 9-5 — it’s an excellent tool for processing a lot of data. That said, at some point, it’s more confusing than helpful to do the analysis in SQL, but it is handy to keep the data in the database for doing the extra set of calculations.

I’ve been trying to figure out how to get a set of date ranges for a Benefit Plan in PeopleSoft with a payroll calendar year. That’s trickier than it sounds, because the company has multiple sets of dates in different database tables — company pay calendar, employment, and plan enrollment. Each of those tables contains many rows, the events that I care about contain a variety of different criteria, so I need to select for those.

I had two key insights:

  • the details of the events don’t matter, so just normalize them by type +1 or -1
  • grab the data from all of the tables and then union them together in the normalized form (event date plus the +/- score)

At that point, the data takes the fom of

Date Score
Date +1
Date +1
Date +1
Date -1

And so on. SQL isn’t great for this, but PL/SQL allows me to walk from the beginning date to the end, pair up my qualifying dates (from the point that the running balance equals three to the point that it isn’t), and then calculate the length of time between them. Not bad.

I just hate that every time I need to use it, I need to reread all the reference docs to remember the language semantics. It’s got a lot of quirks: declarations are done first and separately, there’s no postfix increment operators (++, +=), etc. Perl seems like so much less fuss…