/** * Sets the {@code startDate} property to the earliest start date, and the * {@code endDate} property to the latest end date based on periods. */ private void setEarliestStartDateLatestEndDate() { this.startDate = getEarliestStartDate(); this.endDate = getLatestEndDate(); }
/** * Generates a sub query which provides a view of the data where each row is * ranked by the start date, then end date of the data value period, latest first. * The data is partitioned by data element, org unit, category option combo and * attribute option combo. A column {@code pe_rank} defines the rank. Only data * for the last 10 years relative to the period end date is included. */ private String getLastValueSubquerySql( DataQueryParams params ) { Date latest = params.getLatestEndDate(); Date earliest = addYears( latest, LAST_VALUE_YEARS_OFFSET ); List<String> columns = getLastValueSubqueryQuotedColumns( params ); String fromSourceClause = getFromSourceClause( params ) + " as " + ANALYTICS_TBL_ALIAS; String sql = "(select "; for ( String col : columns ) { sql += col + ","; } sql += "row_number() over (" + "partition by dx, ou, co, ao " + "order by peenddate desc, pestartdate desc) as pe_rank " + "from " + fromSourceClause + " " + "where pestartdate >= '" + getMediumDateString( earliest ) + "' " + "and pestartdate <= '" + getMediumDateString( latest ) + "' " + "and (value is not null or textvalue is not null))"; return sql; }