Turning Point-in-Time Data Into Time Series

Journey through time while standing still.

February 3, 2018


Recently at work I needed to turn different sets of “point-in-time” (PiT) data into time series. Specifically, given rows in a SQL Server database that represent the current state of an entity, how can we show how long that entity has been or was “in process?”

I am sure it is not original, but since I figured this out independently, I thought I would document it here.


The PiT data has to have at least three columns for this technique to work:

  1. Created on - a column indicating the date and time at which the entity was originally created.

  2. Modified on - a column indicating the date and time at which the entity was last altered.

  3. Status - some sort of code indicating whether the entity is still “open” (in-flight, in-process) or else “closed” (finished, expired, cancelled, etc.) This doesn’t have to be a simple yes/no or open/closed status, as we will see.

There are many business entities that have that sort of data - e.g., service tickets in a help desk system, opportunities in a CRM system, loan applications, etc.

Also, we will need a simple table with a series of dates in it. See the next section for that.

Create a Sequence of Dates

I can’t take credit for this - I ripped off the original logic from the accepted answer at this StackOverflow post. However, I just wanted a fixed range, and I chose from January 1, 1980, well before our business data begins, until December 31, 2100, long after I am dead (I will let this be part of someone’s Y2100 problem).

First I created a view:

Note that the above generates the given date range of over 44,000 rows fast - under a second (because there is no I/O involved). However, after some testing it seemed like the view would be too slow (not much the SQL optimizer can do with it), so then I created a materialized view and populated it as follows:

Now we have a simple, keyed table with all the dates from 1/1/1980 through 12/31/2100. Cool.

Convert PiT Rows to Time Series

For example purposes I’ll use opportunities from Microsoft CRM. However, the concept remains the same regardless of your entity, as long as it has the three columns I mentioned above, i.e., “created on,” “modified on,” and “status.” Let’s create and populate the time series table for opportunities.

Note that we use the current date and time if the opportunity is still open, otherwise we used the modifiedon value to indicate when it was closed. For some other entities I have done this for, the CASE statement can get quite long, but at the end, the logic remains:

  1. For “open” status codes, the WHEN returns the current date and time.

  2. For “closed” status codes, the WHEN returns the last modified date and time.

That’s all there is to it.

How Many X Were Open on Any Given Date?

The whole reason I went down this path was then to be able to chart how many of each entity type was open on any given date. That is where the ExplodedDates table comes in to play. For each entity, simply create a view similar to the following:

Basically, for each date in the date range, if is is on or after the opportunity’s “created on” date and it is before or on the “last touched” date (either “modified on” or today’s date), then it is counted as “open” on that day. This yields a series like:

TheDate    OpenOpps
1/1/2017    1455
1/2/2017    1455
1/3/2017    1456
1/4/2017    1453
1/5/2017    1463
1/6/2017    1465
1/7/2017    1465
...lots more rows...
1/27/2018    899
1/28/2018    899
1/29/2018    899
1/30/2018    828
1/31/2018    816
2/1/2018    810
2/2/2018    801
2/3/2018    781


Creating the above takes very little time for each business entity. Then, you can start showing graphs that show counts of the various types of entities in your business that are active by date. It is very handy for dashboards and the like.