Let me break this part down for you:
WHERE t_stamp >= coalesce((
SELECT TOP 1 t_stamp
FROM myTable
WHERE t_stamp < :begin_ts
ORDER BY t_stamp DESC), '1900-01-01')
AND t_stamp <= coalesce((
SELECT TOP 1 t_stamp
FROM myTable
WHERE t_stamp > :end_ts
ORDER BY t_stamp), '2100-01-01')
For lead() to work as we desire, it always needs two rows, the one we are “on”, and the next one. On the last row, lead
is null because there’s no row after it. Which means, to compute durations at the boundaries of our desired time span, we have to include at least one row outside the time span on either end.
We can find the last row before our timespan like so:
SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp < :begin_ts ORDER BY t_stamp DESC
We can find the first row after our timespan like so:
SELECT TOP 1 t_stamp FROM myTable WHERE t_stamp > :end_ts ORDER BY t_stamp
We are allowed to use ORDER BY
in these subqueries because we are using TOP 1
to get just the first result. However, these two queries can return no row at all, which would be a null. So these are wrapped in the coalesce()
function, supplying a far past date or a far future date when necessary.
Using these timestamps in the inner query gives that query all the rows in the desired timespan, plus one row just outside the timespan on either end. That gives the lead()
function something to work with at the boundaries.