//AMIS Technology Blog » lag

Posts tagged lag

image.png

Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions

2
5 months ago
by Lucas Jellema in , ,

After teaching the Advanced SQL Masterclass recently, one of the attendees confronted me with an interesting challenge for me to solve with all the tools I had been discussing all day. This challenge originates in the world of insurance and revolves around policies. Apparently (I am not much of an insurance expert) policies can have periods of inclusions (when they are valid) as well as periods of exclusion (when the policy is definitely not valid, even when there are inclusions that suggest the opposite. The exclusions win, so to say.

Visually, I can describe the situation for one specific policy like this:

Here we see a policy with five inclusions (in green), that partly overlap, as well as four exclusions (in red). The challenge is that we need to find out from a table that contains all periods of inclusion (green) and exclusion (red) what the resulting periods of validity are for the policy. Visually that would be deriving the blue bars in the following figure:

This article describes how this challenge can be approached in SQL.

(more…)

Vacatures bij AMIS services

Finding the longest streak using SQL Analytical Functions

1
2 years ago
by Lucas Jellema in ,

Some IT organizations that I visit have a sign somewhere indicating the number of days that have passed without a major issue. Production sites with proper respect for health and safety to something similar with signs like this one:

In this article, I am wondering about two things:

  • given a list of all incidents, can I easily derive the longest streak of days without accident
  • and the reverse: given a list of events, can I easily find the longest consecutive string of days on which that particular type of event happened (this time not accidents but more positive things like ‘the boss giving a compliment’, ‘an inspection passed’ , ‘a shiny new blog article published’, ‘my neighbour not drinking an alcoholic beverage’ or ‘my son getting up on time’

Using Analytical Functions, it should be fairly easy to find out both things, I suspect – although it turns out that the first is a little more straightforward.

(more…)

Go to Top