Jay Brown
48 lines
1.3 KiB
SQL
48 lines
1.3 KiB
SQL
-- Migration 012: Field Extraction Views
|
|
-- Views for querying current field extractions
|
|
|
|
-- Create view for current (newest) field extraction per document
|
|
CREATE VIEW currentFieldExtractions AS
|
|
SELECT DISTINCT ON (dfe.documentId)
|
|
dfe.id,
|
|
dfe.documentId,
|
|
dfe.fileName,
|
|
dfe.contractTitle,
|
|
dfe.aareteDerivedAmendmentNum,
|
|
dfe.clientName,
|
|
dfe.payerName,
|
|
dfe.payerState,
|
|
dfe.providerState,
|
|
dfe.filenameTin,
|
|
dfe.provGroupTin,
|
|
dfe.provGroupNpi,
|
|
dfe.provGroupNameFull,
|
|
dfe.provOtherTin,
|
|
dfe.provOtherNpi,
|
|
dfe.provOtherNameFull,
|
|
dfe.aareteDerivedEffectiveDt,
|
|
dfe.aareteDerivedTerminationDt,
|
|
dfe.autoRenewalInd,
|
|
dfe.autoRenewalTerm,
|
|
dfev.version,
|
|
dfev.createdBy,
|
|
dfev.createdAt
|
|
FROM documentFieldExtractions dfe
|
|
JOIN documentFieldExtractionVersions dfev
|
|
ON dfev.fieldExtractionId = dfe.id
|
|
ORDER BY dfe.documentId, dfev.id DESC;
|
|
|
|
-- Create extended view with array field count
|
|
CREATE VIEW currentFieldExtractionsWithArrayCount AS
|
|
SELECT
|
|
cfe.*,
|
|
COALESCE(array_counts.arraySize, 0) AS arraySize
|
|
FROM currentFieldExtractions cfe
|
|
LEFT JOIN (
|
|
SELECT
|
|
fieldExtractionId,
|
|
COUNT(*) AS arraySize
|
|
FROM documentFieldExtractionArrayFields
|
|
GROUP BY fieldExtractionId
|
|
) array_counts ON array_counts.fieldExtractionId = cfe.id;
|