
/* Code Fragment 9.14 Extract a backlog from a transaction-time state table. */

SELECT  PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE,
	PROJECTION_UOM, ZONE_CODE, Start_Date AS When_Changed, 'I' as Operation
FROM P_TT AS P1
WHERE NOT EXISTS (	SELECT *
			FROM P_TT AS P2
			WHERE P1.PROJECTION_ID = P2.PROJECTION_ID
			AND P1.Start_Date = P2.Stop_Date )

UNION

SELECT  PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE,
	PROJECTION_UOM, ZONE_CODE, Stop_Date AS When_Changed, 'D' as Operation
FROM P_TT AS P1
WHERE P1.Stop_Date < '9999-12-01'
AND NOT EXISTS (	SELECT *
			FROM P_TT AS P2
			WHERE P1.PROJECTION_ID = P2.PROJECTION_ID
			AND P1.Stop_Date = P2.Start_Date )

UNION

SELECT  P1.PROJECTION_ID, P1.PROJECTION_NAME, P1.PROJECTION_TYPE, P1.SPHEROID_CODE,
	P1.PROJECTION_UOM, P1.ZONE_CODE, P2.Start_Date AS When_Changed, 'U' as Operation
FROM P_TT AS P1, P_TT AS P2
WHERE P1.PROJECTION_ID = P2.PROJECTION_ID
AND P1.Stop_Date = P2.Start_Date 

