Missing approval information PGSQL: Difference between revisions
Jump to navigation
Jump to search
Created page with "{{Under construction | still MySQL queries.}} It is '''important''' that the approval information is set. '''NOTE''' How to fix it is different for each country <pre> SELECT..." |
No edit summary |
||
| Line 1: | Line 1: | ||
{{Under construction | | {{Under construction | will add more queries.}} | ||
It is '''important''' that the approval information is set | {{TOC right}} | ||
[[Image:Missing approval info.png|800px|center]] | |||
<div align="center"> | |||
'' How it should NOT look like'' | |||
</div> | |||
Depending on the reason why the approval information is missing there are different combinations: | |||
* only Approval date missing | |||
* only Approved by missing | |||
* 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__ | |||
<pre> | <pre> | ||
SELECT | SELECT | ||
fieldreport.fieldreport_localid, | "public".fieldreport.fieldreport_localid AS "Form ID", | ||
fieldreport.dateofreport AS | "public".fieldreport.dateofreport AS "Date of Information", | ||
fieldreport.reportreceiveddate AS | "public".fieldreport.reportreceiveddate AS "Data Entry Date", | ||
fieldreport.reportcompleteddate AS | "public".fieldreport.reportcompleteddate AS "Submitted Date", | ||
fieldreport.reportverifieddate AS | "public".fieldreport.reportverifieddate AS "Approved Date", | ||
fieldreport.dataenterer AS | "public".fieldreport.verifiedby AS "Approved by", | ||
"public".fieldreport.dataenterer AS "Data enterer", | |||
workbench_status.enumvalue | |||
FROM | FROM | ||
fieldreport | fieldreport | ||
| Line 18: | Line 31: | ||
fieldreport.verifiedby IS NULL ) | fieldreport.verifiedby IS NULL ) | ||
ORDER BY | ORDER BY | ||
"Form ID" ASC | |||
</pre> | </pre> | ||
If you have any output here you need to take action. The queries below help you to identify for which item(s) the rows in the table ''fieldreport'' need to be updated. | |||
{{NavBox Hub}} | {{NavBox Hub}} | ||
[[Category:NoPublic]] | [[Category:NoPublic]] | ||
[[Category:SQL Queries]] | [[Category:SQL Queries]] | ||
Revision as of 23:37, 13 November 2016
 |
will add more queries. |
How it should NOT look like
Depending on the reason why the approval information is missing there are different combinations:
- only Approval date missing
- only Approved by missing
- 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
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".fieldreport.dataenterer AS "Data enterer", workbench_status.enumvalue FROM fieldreport Inner Join imsmaenum AS workbench_status ON (workbench_status.imsmaenum_guid = fieldreport.workbenchstatusenum_guid) WHERE workbench_status.enumvalue = 'Approved' AND (fieldreport.reportverifieddate IS NULL OR fieldreport.verifiedby IS NULL ) ORDER BY "Form ID" ASC
If you have any output here you need to take action. The queries below help you to identify for which item(s) the rows in the table fieldreport need to be updated.