Page Comparison

SELECT
 l.loanID loanoutID,
 l.loanNumber loanOutNumber,
 (CASE WHEN l.loanPurposeID != 0 THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanpurpose):item:name(' + lower(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.loanPurpose, ' ', '_'), '-', '_'), ':', ''), '.', ''), ',', '')) + ')''' + p.loanPurpose + '''' END) loanPurpose,
 (CASE WHEN l.constituentID != -1 THEN l.constituentID END) borrower,
 l.beginISODate loanOutDate,
 l.endISODate loanReturnDate,
 (CASE WHEN l.requestDate IS NOT NULL OR l.requestedBy IS NOT NULL THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanstatus):item:name(requested)''requested''' END) loanStatus,
 CONVERT(VARCHAR, l.requestDate, 101) loanStatusDate,
 (CASE WHEN l.requestedBy IS NOT NULL THEN 'Requested by: ' + l.requestedBy END) loanStatusNote,
 l.description loanOutNote_1,
 (CASE WHEN l.contact IS NOT NULL THEN 'Contact: "' + l.contact + '"' END) loanOutNote_2,
 l.remarks loanOutNote_3
FROM
 Loans l
 JOIN LoanPurposes p ON l.loanPurposeID = p.loanPurposeID
WHERE
 l.loanin = 0
 ;

SELECT
 CAST (x.objectID AS VARCHAR) objectID,
 x.loanID loanoutID
FROM
 LoanObjXrefs x
 JOIN loans l ON x.loanID = l.loanID
WHERE
 l.loanin = 0
;

SELECT
 x.ID loanoutID,
 x.constituentID borrowersAuthorizer
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 0
 AND x.roleID = 71
;

SELECT
 x.ID loanoutID,
 x.constituentID borrowersContact
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 0
 AND x.roleID IN (79, 82)
;

SELECT
 l.loanID loaninID,
 l.loanNumber loanInNumber,
 (CASE WHEN l.loanPurposeID != 0 THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanpurpose):item:name(' + lower(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.loanPurpose, ' ', '_'), '-', '_'), ':', ''), '.', ''), ',', '')) + ')''' + p.loanPurpose + '''' END) loanPurpose,
 (CASE WHEN l.constituentID != -1 THEN l.constituentID END) borrower, --?
 l.beginISODate loanInDate,
 l.endISODate loanReturnDate,
 (CASE WHEN l.requestDate IS NOT NULL OR l.requestedBy IS NOT NULL THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(loanstatus):item:name(requested)''requested''' END) loanStatus,
 CONVERT(VARCHAR, l.requestDate, 101) loanStatusDate,
 (CASE WHEN l.requestedBy IS NOT NULL THEN 'Requested by: ' + l.requestedBy END) loanStatusNote,
 l.description loanInNote_1,
 (CASE WHEN l.contact IS NOT NULL THEN 'Contact: "' + l.contact + '"' END) loanInNote_2,
 l.remarks loanInNote_3
FROM
 Loans l
 JOIN LoanPurposes p ON l.loanPurposeID = p.loanPurposeID
WHERE
 l.loanin = 1
 ;

SELECT
 CAST (x.objectID AS VARCHAR) objectID,
 x.loanID loaninID
FROM
 LoanObjXrefs x
 JOIN loans l ON x.loanID = l.loanID
WHERE
 l.loanin = 1
;

SELECT
 x.ID loaninID,
 x.constituentID lendersContact
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN loans l ON x.ID = l.loanID
WHERE
 x.tableID = 81
 AND x.active = 1
 AND l.loanin = 1
 AND x.roleID IN (76, 77, 79)
;