QuerySurge Technical Whitepaper No. 10
Introduction
A common need for some QuerySurge projects is to measure time differences or lags between events. An example might be whether the difference between an order date and the corresponding shipping date exceeds a specified value. As you're likely aware, QuerySurge by design does not have “built-in” tests – we want to put the full power of your SQL dialects in your hands to craft the tests that you need. You are probably also aware that the minimal executable unit in QuerySurge is a QueryPair – a pair of logically related queries. In other words, QueryPairs are "binary" - because each is composed of two queries. So, how does a user implement a check for a time lag between events captured in rows on a single table in a QueryPair? The answer is to set up a "one-sided" or "business rule" test.
Writing a Lag-time Checking Query
The first thing we need to consider is the syntax for a time-lag-checking strategy. Assuming that we have columns in our table for the order date and the ship date, this can be done with standard syntax. Consider the following ORDERPLACEMENT table, containing ORDER_DATE and SHIP_DATE columns.
| ID | ORDER_DATE | SHIP_DATE |
| 1100 | 2015-07-31 | 2015-08-06 |
| 1102 | 2016-10-27 | 2016-10-28 |
| 1103 | 2017-03-19 | NULL |
| 1104 | 2017-02-06 | 2017-02-08 |
| 1105 | 2017-05-05 | 2017-05-09 |
| 1106 | 2016-05-03 | 2016-05-10 |
| 1107 | 2017-01-27 | 2017-01-30 |
| 1108 | 2017-04-10 | NULL |
| 1121 | 2016-12-11 | 2016-12-13 |
| 1130 | 2017-01-20 | 2017-01-25 |
If we want to check the lag-time between the ORDER_DATE and the SHIP_DATE for orders, we can write the following query. Note that we exclude any rows where the SHIP_DATE is null, since these transactions have not completed:SELECT op.ID, (op.SHIP_DATE - op.ORDER_DATE) LAGTIME
FROM orderplacement op
WHERE op.SHIP_DATE IS NOT NULL;
Note: These examples are worked in Oracle 10g. The equivalent syntax for your database may differ.
The results of this query are:
| ID | LAGTIME |
| 1100 | 6 |
| 1102 | 1 |
| 1104 | 2 |
| 1105 | 4 |
| 1106 | 7 |
| 1107 | 3 |
| 1121 | 2 |
| 1130 | 5 |
The lag-time calculated in this query is in the default time unit (days).
Implementing a Lag-time Checking Test in a QueryPair
Now that we have shown how to calculate lag times between order placement and order shipping, how can we formulate this as a business rule test in QuerySurge? The answer is straightforward: we can modify our lag-time checking query to return a value indicating whether a row's lag-time exceeds a pre-selected maximum value or not. This query can be used as the Source side of a QueryPair. On the Target side, we just use a static query to return the passing value on each row.
In order to modify our lag-time checking query for QuerySurge, we’ll use the CASE syntax to return a value indicating a passing condition for those rows with an acceptable lag-time, and a different value for those rows with an unacceptable lag-time. In this example, if the lag-time for any transaction exceeds 5 days, then the query returns a string value of ‘> 5’ indicating a failing condition. If the lag-time is smaller than the maximum value, then the query returns a string value of '< 5' indicating a passing condition. The reason for this type of return value is to make the results readable.
We can write for the QueryPair Source query:SELECT op.ID,
CASE
WHEN (op.SHIP_DATE - op.ORDER_DATE) > 5 then '> 5'
ELSE '< 5'
END LAGTIME
FROM orderplacement op
WHERE op.SHIP_DATE IS NOT NULL
ORDER BY op.ID;
Note: The ID column (or another shared key) is included to ensure that each row is unique. For a full discussion of this, see this article.
Since we want rows to fail where the Source query returns a string of '> 5', we need write a query on the Target side that returns the same number of rows as the Source query, returning a '< 5' (the passing value) for each row:SELECT op.ID, '< 5' ALLOWEDLAGTIMEMAX
FROM orderplacement op
WHERE op.SHIP_DATE IS NOT NULL
ORDER BY op.ID;
When run, the resulting Failure tab is shown in Figure 1, with the two rows that fail the lag-time rule.
Figure 1. The time lag test in QuerySurge. On the Source side, if a row exceeds the allowed maximum time lag, the Source query returns a string value of ‘> 5’ (the failing condition); the rows that fail this test are clearly indicated. The Target query always returns ‘< 5’ (the passing condition) for all rows.
Note: While in this example we have used string values of ‘> 5’ for the failing condition and ‘< 5’ for the passing condition, these choices are made simply to provide clarity in how the results appear. For your tests, you can and should implement using return values and/or data types for the passing and failing conditions that are most appropriate for your needs.