Listing 2: Query That Calculates Correct Datetime Differences

BEGIN CALLOUT A
SELECT keycol, from_ts, to_ts, sgn, y, m, d,
  s / 3600      AS h,
  s % 3600 / 60 AS mi,
  s % 60        AS s,
  (1000 + DATEPART(ms, to_ts) - DATEPART(ms, from_ts)) % 1000 AS ms
FROM
END CALLOUT A
(
BEGIN CALLOUT B
SELECT keycol, from_ts, to_ts, sgn,
  y,
  m - DATEDIFF(month, from_ts, y_ts)  AS m,
  d - DATEDIFF(day,   from_ts, m_ts) AS d,
  DATEDIFF(second, d_ts, to_ts) AS s
FROM
END CALLOUT B
(
BEGIN CALLOUT C
SELECT *,
  DATEADD(year,  y, from_ts) AS y_ts,
  DATEADD(month, m, from_ts) AS m_ts,
  DATEADD(day,   d, from_ts) AS d_ts
FROM
END CALLOUT C
(
BEGIN CALLOUT D
SELECT keycol, from_ts, to_ts, sgn,
  y - CASE WHEN DATEADD(year,  y, from_ts) > to_ts
    THEN 1 ELSE 0 END AS y,
  m - CASE WHEN DATEADD(month, m, from_ts) > to_ts
    THEN 1 ELSE 0 END AS m,
  d - CASE WHEN DATEADD(day,   d, from_ts) > to_ts
    THEN 1 ELSE 0 END AS d
FROM
END CALLOUT D
(
BEGIN CALLOUT E
SELECT *,
  DATEDIFF(year,  from_ts, to_ts) AS y,
  DATEDIFF(month, from_ts, to_ts) AS m,
  DATEDIFF(day,   from_ts, to_ts) AS d
FROM
END CALLOUT E
(
BEGIN CALLOUT F
SELECT keycol,
  CASE WHEN from_ts <= to_ts THEN from_ts ELSE to_ts   END AS from_ts,
  CASE WHEN from_ts <= to_ts THEN to_ts   ELSE from_ts END AS to_ts,
  CASE WHEN from_ts <= to_ts THEN 1 WHEN to_ts < from_ts THEN -1 END AS sgn
FROM dbo.TimeStamps
END CALLOUT F
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5;
GO