Open main menu

Changes

Missing approval information

6,072 bytes added, 04:35, 16 November 2016
no edit summary
* both missing.
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__
fieldreport.verifiedby AS `Approved by`,
fieldreport.dataenterer AS `Data enterer`,
workbench_statusimsmaenum.enumvalue
FROM
fieldreport
Inner Join INNER JOIN imsmaenum AS workbench_status ON (workbench_statusimsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid)
WHERE
workbench_statusimsmaenum.enumvalue = 'Approved' AND(fieldreport.reportverifieddate IS NULL ORfieldreport.verifiedby IS NULL )
ORDER BY
fieldreport.fieldreport_localid `Form ID` ASC
</pre>
=== Accident===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
accidentinfoversion.dataentrydate AS InfoDate,
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 = 0
ORDER BY
`Form ID` ASC
</pre>
=== Hazard ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
hazardinfoversion.dataentrydate AS InfoDate,
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 = 0
</pre>
=== Hazard Reducation===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
hazreducinfoversion.dataentrydate AS InfoDate,
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 = 0
</pre>
=== Location===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
locationinfoversion.dataentrydate AS InfoDate,
locationinfoversion.dataenterer AS InfoEnterer
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN locationinfoversion ON locationinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
locationinfoversion.link_only = 0
ORDER BY
`Form ID` ASC
</pre>
=== MRE===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
mreinfoversion.dataentrydate AS InfoDate,
mreinfoversion.dataenterer AS InfoEnterer
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN mreinfoversion ON mreinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
mreinfoversion.link_only = 0
ORDER BY
`Form ID` ASC
</pre>
=== QM===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
qainfoversion.dataentrydate AS InfoDate,
qainfoversion.dataenterer AS InfoEnterer
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN qainfoversion ON qainfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
qainfoversion.link_only = 0
ORDER BY
`Form ID` ASC
</pre>
=== Victim===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue,
fieldreport.dataentrydate AS `FR Data date`,
fieldreport.dataenterer AS `FR Data enterer`,
victiminfoversion.dataentrydate AS InfoDate,
victiminfoversion.dataenterer AS InfoEnterer
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN victiminfoversion ON victiminfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
victiminfoversion.link_only = 0
ORDER BY
`Form ID` ASC
</pre>
Alnaucler
NoPublic, bureaucrat, administrator
6,632
edits
Retrieved from "http://mwiki.gichd.org:8090/IM/Special:MobileDiff/22473"