=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java	2013-07-10 05:10:43 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java	2013-07-15 06:18:13 +0000
@@ -775,9 +775,8 @@
             sql = sql
                 .replace( CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR + "_" + key, minus2SQLMap.get( key ) );
         }
-
+        
         return sql + " ) ";
-
     }
 
     /**
@@ -789,14 +788,14 @@
     {
         String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS";
 
-        String sql = " " + keyExist + " ( SELECT _psi.programstageinstanceid "
+        String sql = " " + keyExist + " ( SELECT * "
             + "FROM patientdatavalue _pdv inner join programstageinstance _psi "
             + "ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi "
             + "ON _pi.programinstanceid=_psi.programinstanceid "
             + "WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=" + dataElementId
             + "  AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ")  "
-            + "AND _pi.programid = " + programId + " AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '"
-            + endDate + "') ";
+            + "  AND _pi.programid = " + programId + " AND _psi.executionDate>='" + startDate
+            + "' AND _psi.executionDate <= '" + endDate + "' ";
 
         if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
         {
@@ -840,8 +839,8 @@
     private String getConditionForPatient( Collection<Integer> orgunitIds, String operator, String startDate,
         String endDate )
     {
-        String sql = " EXISTS ( SELECT _p.patientid FROM patient _p " + "WHERE _p.patientid = pi.patientid "
-            + "AND ( _p.registrationdate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+        String sql = " EXISTS ( SELECT * " + "FROM patient _p " + "WHERE _p.patientid = pi.patientid "
+            + "AND _p.registrationdate>='" + startDate + "' AND _p.registrationdate<='" + endDate + "' "
             + "AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") ";
 
         return sql;
@@ -893,7 +892,13 @@
     private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property )
     {
         String sql = " EXISTS ( SELECT _pi.programinstanceid FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND "
-            + "( psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') AND " + property + " ";
+            + "psi.executionDate >= '"
+            + startDate
+            + "' AND psi.executionDate <= '"
+            + endDate
+            + "' AND "
+            + property
+            + " ";
 
         return sql;
     }
@@ -910,7 +915,7 @@
             + "INNER JOIN programstageinstance _psi ON _pi.programinstanceid=_psi.programinstanceid "
             + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _pi.programid=" + programId + " "
             + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
-            + ") AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ";
+            + ") AND _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' ";
 
         return sql;
     }
@@ -925,8 +930,13 @@
     {
         String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programinstance as _pi INNER JOIN programstageinstance _psi "
             + "ON _pi.programinstanceid = _psi.programinstanceid WHERE _psi.programstageinstanceid=psi.programstageinstanceid "
-            + "AND _psi.programstageid=" + programStageId + " AND (_psi.executionDate BETWEEN '" + startDate
-            + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+            + "AND _psi.programstageid="
+            + programStageId
+            + "  AND _psi.executiondate >= '"
+            + startDate
+            + "' AND _psi.executiondate <= '"
+            + endDate
+            + "' AND _psi.organisationunitid in ("
             + TextUtils.getCommaDelimitedString( orgunitIds ) + ")  ";
 
         return sql;
@@ -944,7 +954,7 @@
         String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance as _psi "
             + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.organisationunitid in ("
             + TextUtils.getCommaDelimitedString( orgunitIds ) + ") and _psi.programstageid = " + programStageId + " "
-            + "AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+            + "AND _psi.executionDate >= '" + startDate + "' AND _psi.executionDate <= '" + endDate + "' "
             + "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid "
             + "HAVING count(_psi.programstageinstanceid) ";
 
@@ -960,9 +970,9 @@
     private String getConditionForProgramStageProperty( String property, String operator,
         Collection<Integer> orgunitIds, String startDate, String endDate )
     {
-        String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance _psi "
-            + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND (_psi.executionDate BETWEEN '"
-            + startDate + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+        String sql = " EXISTS ( SELECT * FROM programstageinstance _psi "
+            + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.executiondate >= '" + startDate
+            + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in ("
             + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND " + property + " ";
 
         return sql;
@@ -987,7 +997,8 @@
 
         sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance _psi "
             + " WHERE _psi.organisationunitid=ou.organisationunitid AND _psi.programstageid = " + programStageId
-            + " AND _psi.completed=true AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ) ";
+            + " AND _psi.completed=true AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '"
+            + endDate + "' ) ";
 
         return sql;
     }
@@ -995,7 +1006,7 @@
     private String getConditionForMinusDataElement( Collection<Integer> orgunitIds, Integer programStageId,
         Integer dataElementId, String compareSide, String startDate, String endDate )
     {
-        return " EXISTS ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv inner join programstageinstance _psi "
+        return " EXISTS ( SELECT_pdv.value FROM patientdatavalue _pdv inner join programstageinstance _psi "
             + "                         ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
             + "                 JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
             + "           WHERE psi.programstageinstanceid=_pdv.programstageinstanceid "
@@ -1010,7 +1021,7 @@
         String dataElementId1, String programStageId2, String dataElementId2, String compareSide, String startDate,
         String endDate )
     {
-        return " EXISTS ( SELECT * FROM ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv "
+        return " EXISTS ( SELECT * FROM ( SELECT _pdv.value FROM patientdatavalue _pdv "
             + "                 INNER JOIN programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
             + "                 JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
             + "           WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid= "
@@ -1018,10 +1029,15 @@
             + "                 AND _psi.organisationunitid in ("
             + TextUtils.getCommaDelimitedString( orgunitIds )
             + ") "
-            + "                 AND _psi.programstageid = " + programStageId1
-            + "                 AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate
-            + "') ) AS d1 cross join "
-            + "         (  SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
+            + "                 AND _psi.programstageid = "
+            + programStageId1
+            + "                 AND _psi.executionDate>='"
+            + startDate
+            + "'  "
+            + "                 AND _psi.executionDate <= '"
+            + endDate
+            + "' ) AS d1 cross join "
+            + "         (  SELECT _pdv.value FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
             + "                        ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
             + "                  JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
             + "           WHERE psi.programstageinstanceid=_pdv.programstageinstanceid and _pdv.dataelementid= "
@@ -1029,9 +1045,14 @@
             + "                 AND _psi.organisationunitid in ("
             + TextUtils.getCommaDelimitedString( orgunitIds )
             + ") "
-            + "                 AND _psi.programstageid =  " + programStageId2
-            + "                 AND( _psi.executionDate BETWEEN '" + startDate  + "' AND '" + endDate
-            + "') ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
+            + "                 AND _psi.programstageid =  "
+            + programStageId2
+            + "                 AND _psi.executionDate>='"
+            + startDate
+            + "'  "
+            + "                 AND _psi.executionDate <= '"
+            + endDate
+            + "' ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
     }
 
     /**