NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
* both missing.
Common user in ''fieldreport.dataenterer'' when the data has been entered by using migration/import scripts is migration/import. The solution is of course depending on what is missing. It is '''important''' that the approval information is set and '''only''' rows with workbench status = '''Approved''' are included.
==Missing approval information ==__NOEDITSECTION__
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".fieldreport.dataenterer AS "FR Data enterer",workbench_statusimsmaenum.enumvalue
FROM
fieldreport
Inner Join imsmaenum AS workbench_status ON (workbench_statusimsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid)
WHERE
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL )
=== Accident===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".accidentinfoversion.dataentrydate AS "InfoDate",
"public".accidentinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN accidentinfoversion ON accidentinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
accidentinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Hazard ===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazardinfoversion.dataentrydate AS "InfoDate",
"public".hazardinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazardinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Hazard Reducation===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazreducinfoversion.dataentrydate AS "InfoDate",
"public".hazreducinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazreducinfoversion ON fieldreport.fieldreport_guid = hazreducinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazreducinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Location===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".locationinfoversion.dataentrydate AS "InfoDate",
"public".locationinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN locationinfoversion ON fieldreport.fieldreport_guid = locationinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
locationinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== MRE===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".mreinfoversion.dataentrydate AS "InfoDate",
"public".mreinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN mreinfoversion ON fieldreport.fieldreport_guid = mreinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
mreinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== QM===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".qainfoversion.dataentrydate AS "InfoDate",
"public".qainfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
qainfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Victim===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".victiminfoversion.dataentrydate AS "InfoDate",
"public".victiminfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
victiminfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>