Page Comparison

SELECT
 d.deaccessionID objectexitID,
 'EX' + o.ObjectNumber exitNumber,
 (CASE WHEN d.dispositionMethod NOT IN (0, 9, 10) THEN lower(m.dispositionMethod) END) exitReason
FROM
 ObjDeaccession d
 JOIN Objects o ON d.objectID = o.objectID
 JOIN DispositionMethods m ON d.dispositionMethod = m.dispMethodID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
;

SELECT
 CAST (d.objectID AS VARCHAR) objectID,
 d.deaccessionID objectexitID
FROM
 ObjDeaccession d
WHERE d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
;

SELECT
 d.deaccessionID objectexitID,
 CONVERT (VARCHAR, d.saleDate, 101) exitDate
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.saleDate IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 CONVERT (VARCHAR, CASE WHEN x.dateBegin != 0 THEN x.dateBegin WHEN x.displayDate IS NOT NULL AND LEN(x.displayDate) > 0 THEN substring(x.displayDate, len(x.displayDate)-4, 5) END) exitDate
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
 AND (x.dateBegin != 0 OR x.displayDate IS NOT NULL)
;

SELECT
 d.deaccessionID objectexitID,
 (CASE WHEN d.recipientConID != -1 THEN d.recipientConID END) currentOwner
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.recipientConID != -1
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 x.constituentID currentOwner
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
;

SELECT
 d.deaccessionID objectexitID,
 d.terms exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.terms IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 (CASE WHEN d.proceedsRcvdDate IS NOT NULL THEN 'TMS ProceedsRcvdDate: ' + CONVERT (VARCHAR, d.proceedsRcvdDate, 101) END) exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.proceedsRcvdDate IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 d.remarks exitNote
FROM
 ObjDeaccession d
WHERE
 d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 d.deaccessionID objectexitID,
 'TMS DeaccessionRecipient: ' + x.remarks exitNote
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN objDeaccession d ON x.ID = d.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND d.objectID IN (SELECT o.objectid FROM objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0)
 AND lower(r.role) IN ('deaccession recipient')
 AND x.remarks IS NOT NULL
;