Nsryjdtyk

I am working on Spring MVC based web application I implemented the graphical representation of data relevant for my client.
its very simple I need to illustrate the counts of registered Employees and Verified Employees on per day registration and verification basis.

I wrote a below query,

 SELECT monthyear 

                        ,SUM(CASE WHEN type='1' THEN total ELSE 0 END )as tot_ver 

                        ,SUM(CASE WHEN type='2' THEN total ELSE 0 END )as tot_reg   

                    FROM ( 

                          SELECT  

                            TO_CHAR(verify_date,'YYYY-mm-dd') as monthyear 

                            ,SUM(CASE WHEN verify_status=1  THEN 1 ELSE 0 END )as total 

                            ,'1' as type  

                            FROM EMPLOYEE 

                            GROUP BY TO_CHAR(verify_date,'YYYY-mm-dd')  

                        UNION ALL 

                         SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear  

                         ,SUM(CASE WHEN emp_id!=0  THEN 1 ELSE 0 END )as total 

                         ,'2' as type 

                         FROM EMPLOYEE 

                        GROUP BY TO_CHAR(add_date,'YYYY-mm-dd') 

                       )  

                    where monthyear IS NOT NULL    

                    GROUP BY monthyear 

                 ORDER BY monthyear 

And JAVA CODE

 public TwoValueBean totRegisterUser() {

        String query = " SELECT monthyear"

                + "         ,SUM(CASE WHEN type='1' THEN total ELSE 0 END )as tot_ver"

                + "         ,SUM(CASE WHEN type='2' THEN total ELSE 0 END )as tot_reg  "

                + "     FROM ("

                + "           SELECT "

                + "             TO_CHAR(verify_date,'YYYY-mm') as monthyear"

                + "             ,SUM(CASE WHEN verify_status=1  THEN 1 ELSE 0 END )as total"

                + "             ,'1' as type "

                + "             FROM EMPLOYEE"

                + "             GROUP BY TO_CHAR(verify_date,'YYYY-mm') "

                + "         UNION ALL"

                + "          SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear "

                + "          ,SUM(CASE WHEN emp_id!=0  THEN 1 ELSE 0 END )as total"

                + "          ,'2' as type"

                + "          FROM EMPLOYEE"

                + "         GROUP BY TO_CHAR(add_date,'YYYY-mm')"

                + "        ) "

                + "     where monthyear IS NOT NULL   "

                + "     GROUP BY monthyear"

                + "  ORDER BY monthyear";

        MapSqlParameterSource param = new MapSqlParameterSource();

        TwoValueBean bean = new TwoValueBean();

        try {

            List<TotalRegisterGraphData> list = getNamedParameterJdbcTemplate().query(query, param, new RowMapper<TotalRegisterGraphData>() {



                @Override

                public TotalRegisterGraphData mapRow(ResultSet rs, int rowNo) throws SQLException {

                    TotalRegisterGraphData regData = new TotalRegisterGraphData();

                    regData.setRegMonth(rs.getString("monthyear"));

                    regData.setTotRegister(rs.getLong("tot_reg"));

                    regData.setTotVerify(rs.getLong("tot_ver"));

                    return regData;

                }

            });

            if (list != null && list.size() > 0) {

                String reqDate = "";

                List<String> val1 = new ArrayList<String>();

                List<String> val2 = new ArrayList<String>();

                List<String> val3 = new ArrayList<String>();

                int currentVer = 0;

                int currentReg = 0;

                for (TotalRegisterGraphData data : list) {

                    currentReg += data.getTotRegister();

                    currentVer += data.getTotVerify();

                    val1.add("'" +data.getRegMonth()+ "'");

                    val2.add(String.valueOf(currentReg));

                    val3.add(String.valueOf(currentVer));

                }

                bean.setVal1(StringProcessorUtil.arrayToString(val1.toArray(new String{}), null));

                bean.setVal2(StringProcessorUtil.arrayToString(val2.toArray(new String{}), null));

                bean.setVal3(StringProcessorUtil.arrayToString(val3.toArray(new String{}), null));

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

        return bean;

    }

}

As you people can see,query fetches the data on behalf of the day of registration, but it is also point to be notice, it add up the count, as soon as user getting registered it shows the maximum count at particular date.

What I want, i need the exact registered employee count on specific day.

For example: if 50 employee will be register on 24-11-2018 then the count should be illustrated as 50 , not 50 + all previous registration.

Similar case arise for Verified Employee.

Registration is first phase (In query, tot_reg, holds the count)
Verification is final phase (In query, tot_ver, holds the count)

Registration can be get by add_date
and
verification can be get by verify_date.

For more clarity i also attached the graph image n which the blue lines show the registered employees and black lines shows the verified employee count, but you people can observe than, it shows the maximum count on specific date.

for example.
if 1 person is registered then it shows 1 on 01-01-2018
if 1 more person registered on next two days, then it shows 2
if 2 people registered on 05-01-2018, then it shows 4

I don't want this to be happen, I want per day user registered count.

if 10 person registered on 08-01-2018, then is should shows 10
if another 20 person registered on 09-01-2018, then it shows 20
similarly for verified employee

Your SQL query returns the data you're requesting, i.e. it returns the day (which you have confusingly called monthyear) and the count of employees registered/verified on that day

It is your Java code that is accumulating the counts

Change these:

currentReg += data.getTotRegister();

currentVer += data.getTotVerify();

To this:

currentReg = data.getTotRegister();

currentVer = data.getTotVerify();

Note, there appears to be a bug in the query:

SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear --month precision, will give repeated rows

...

GROUP BY TO_CHAR(add_date,'YYYY-mm-dd') --day precision

And your query as pasted in the top box, doesn't match the query in the java code