Changes

Jump to: navigation, search

Missing Date of Information PGSQL

1,019 bytes added, 07:33, 11 November 2016
no edit summary
{{Under construction| The SQL below are in MySQL.}}
{{TOC right}}
{{Note | The Summary item/window calculation is '''depending''' on that ''Date of Information'' is filled in and the rules for if a DEF is Unapprovable are also depending on ''Date of Information''. There is a MySQL version of this page '''[[Missing Date of Information| too]]'''.}}
==Missing Date of Information ==__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.dataenterer AS `"Data enterer`","public".fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`"
FROM
"public".fieldreportINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL
ORDER BY
imsmaenum.enumvalue "WB status" DESC, `"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".hazardinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportINNER JOIN "public".hazardinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazardinfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".hazardinfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".accidentinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".accidentinfoversion ON "public".fieldreport.fieldreport_guid = "public".accidentinfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".accidentinfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`",hazreducinfoversion"public".imsmaenum.dataentrydate enumvalue AS InfoVerDate"WB status", "public".hazreducinfoversion.enddatedataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".hazreducinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazreducinfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".hazreducinfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".locationinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".locationinfoversion ON "public".fieldreport.fieldreport_guid = "public".locationinfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".locationinfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".mreinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".mreinfoversion ON "public".fieldreport.fieldreport_guid = "public".mreinfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".mreinfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".qainfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".qainfoversion ON "public".fieldreport.fieldreport_guid = "public".qainfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".qainfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<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`",fieldreport"public".dataenterer AS `Data enterer`,fieldreport.verifiedby AS `"Approved by`","public".imsmaenum.enumvalue AS `"WB status`","public".victiminfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreportInner Join INNER JOIN "public".victiminfoversion ON "public".fieldreport.fieldreport_guid = "public".victiminfoversion.fieldreport_guidINNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND"public".victiminfoversion.link_only = '0f'
ORDER BY
"InfoVerDate " ASC,`"Form ID` " ASC
</pre>
<pre>
update fieldreport
Inner Join hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
SET
fieldreport.dateofreport = hazardinfoversion.dataentrydateFROM imsmaenum, hazardinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid ANDfieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid ANDfieldreport.dateofreport IS NULL ANDhazardinfoversion.link_only = '0f' ANDhazardinfoversion.dataentrydate IS NOT NULL< fieldreport.reportreceiveddate
</pre>
Alnaucler
NoPublic, bureaucrat, administrator
6,632
edits
Retrieved from "http://mwiki.gichd.org:8090/IM/Special:MobileDiff/22423"

Navigation menu