private String getProgramIndicatorEventInProgramStageSql(ProgramIndicator programIndicator, Date reportingStartDate, Date reportingEndDate ) { Assert.isTrue( programIndicator.hasEventDateCohortBoundary(), "Can not get event date cohort boundaries for program indicator:" + programIndicator.getUid() ); Map<String, Set<AnalyticsPeriodBoundary>> map = programIndicator.getEventDateCohortBoundaryByProgramStage(); final SimpleDateFormat format = new SimpleDateFormat(); format.applyPattern( Period.DEFAULT_DATE_FORMAT ); String sql = ""; for ( String programStage : map.keySet() ) { Set<AnalyticsPeriodBoundary> boundaries = map.get( programStage ); String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); sql += " (select count(*) from " + eventTableName + " where " + eventTableName + ".pi = " + ANALYTICS_TBL_ALIAS + ".pi and executiondate is not null "; for ( AnalyticsPeriodBoundary boundary : boundaries ) { sql += " and executiondate " + ( boundary.getAnalyticsPeriodBoundaryType().isStartBoundary() ? ">" : "<" ) + " cast( '" + format.format( boundary.getBoundaryDate( reportingStartDate, reportingEndDate ) ) + "' as date )"; } sql += ") > 0"; } return sql; }