I have been trying to find the table that stores the time stamp for an uploaded aggregate report in dhis2. I need that data for a report i am creating using SQL view. I managed to find a table called datavalueaudit that has a time stamp column but every query I try pulls the time stamp for the dataelements that make up the aggregate report. I am still a newbie at both sql and dhis2 so i do not know how to go about solving the problem. I am trying to modify a query that I kind of understand but still find kinda complicated
The following is the original query
SELECT DISTINCT p.startdate, prov.name AS province, par.name AS cheifdom, ou.name AS village, regexp_replace(ou.phonenumber, '+260', '0'), CASE WHEN b.reported IS NULL THEN 0::integer ELSE 1::integer END AS reported FROM datasetsource dss CROSS JOIN (SELECT DISTINCT periodid, startdate FROM period WHERE startdate <= now() AND periodtypeid = (SELECT periodtypeid FROM periodtype WHERE name ='Monthly') ORDER BY startdate DESC OFFSET 1 LIMIT 12) p LEFT JOIN (SELECT DISTINCT dv.sourceid, dv.periodid, TRUE AS reported FROM datavalue dv INNER JOIN (SELECT DISTINCT periodid, startdate FROM period WHERE startdate <= now() AND periodtypeid = (SELECT periodtypeid FROM periodtype WHERE name ='Monthly') ORDER BY startdate DESC OFFSET 1 LIMIT 12) a ON dv.periodid = a.periodid WHERE dv.dataelementid IN (SELECT DISTINCT dataelementid FROM datasetmembers WHERE datasetid = (SELECT datasetid FROM dataset WHERE uid = 'Hbcr2fLc9jM'))) b ON b.sourceid = dss.sourceid AND b.periodid = p.periodid INNER JOIN organisationunit ou ON dss.sourceid = ou.organisationunitid INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid INNER JOIN organisationunit prov ON par.parentid = prov.organisationunitid INNER JOIN _periodstructure ps ON p.periodid = ps.periodid WHERE dss.datasetid = (SELECT datasetid FROM dataset WHERE uid = 'Hbcr2fLc9jM') ORDER BY prov.name, par.name, ou.name, p.startdate
The following is the one I tried modifying
SELECT DISTINCT p.startdate, prov.name AS province, par.name AS cheifdom, ou.name AS village, regexp_replace(ou.phonenumber, '+260', '0'), CASE WHEN b.reported IS NULL THEN 0::integer ELSE 1::integer END AS reported, dva.timestamp AS "Reports On Time" FROM datasetsource dss CROSS JOIN (SELECT DISTINCT periodid, startdate FROM period WHERE startdate <= now() AND periodtypeid = (SELECT periodtypeid FROM periodtype WHERE name ='Monthly') ORDER BY startdate DESC OFFSET 1 LIMIT 12) p LEFT JOIN (SELECT DISTINCT dv.sourceid, dv.periodid, TRUE AS reported FROM datavalue dv INNER JOIN (SELECT DISTINCT periodid, startdate FROM period WHERE startdate <= now() AND periodtypeid = (SELECT periodtypeid FROM periodtype WHERE name ='Monthly') ORDER BY startdate DESC OFFSET 1 LIMIT 12) a ON dv.periodid = a.periodid WHERE dv.dataelementid IN (SELECT DISTINCT dataelementid FROM datasetmembers WHERE datasetid = (SELECT datasetid FROM dataset WHERE uid = 'Hbcr2fLc9jM'))) b ON b.sourceid = dss.sourceid AND b.periodid = p.periodid LEFT JOIN ( SELECT DISTINCT dv.timestamp, dv.periodid, TRUE AS reported FROM datavalueaudit dv INNER JOIN (SELECT DISTINCT periodid, startdate FROM period WHERE startdate <= now() AND periodtypeid = (SELECT periodtypeid FROM periodtype WHERE name ='Monthly') ORDER BY startdate DESC OFFSET 1 LIMIT 12) a ON dv.periodid = a.periodid WHERE dv.dataelementid IN (SELECT DISTINCT MAX(dataelementid) FROM datasetmembers WHERE datasetid = '29827' GROUP BY datasetid)) k ON k.periodid = p.periodid INNER JOIN organisationunit ou ON dss.sourceid = ou.organisationunitid LEFT JOIN datavalueaudit dv ON dss.sourceid = dv.organisationunitid INNER JOIN datavalueaudit dva ON k.timestamp = dva.timestamp INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid INNER JOIN organisationunit prov ON par.parentid = prov.organisationunitid INNER JOIN _periodstructure ps ON p.periodid = ps.periodid WHERE dss.datasetid = (SELECT datasetid FROM dataset WHERE uid = 'Hbcr2fLc9jM') ORDER BY prov.name, par.name, ou.name, p.startdate, dva.timestamp
The query I tried modifying only pulls the time stamp of when all the dataelements in the dataset of the completed aggregate report were uploaded instead of the time stamp of when just the completed aggregate report was uploaded
I would like to add a new column that pulls the time stamp data from the table that stores it but only for when a completed aggregate report*(record) has been uploaded.
