CODEWITHSUNDEEP
javadistincthibernate-criteria
Sanal S
Sanal S

Reputation: 1152

Get Two different count from one field, for a distinct date using hibernate criteria

I have a table with values, (SAMPLE VALUES)

 ____________________
|DATE           FORM |
|2016-01-21     NEW  |
|2016-01-21     OLD  |
|2016-01-21     NEW  |
|2016-01-22     NEW  |
|2016-01-22     OLD  |
|2016-01-24     OLD  |
|2016-01-24     NEW  |
|2016-01-25     NEW  |
|2016-01-26     OLD  |
|____________________|

How can i write hibernate criteria to get the count of FORM, with each NEW and OLD in separate columns for each date results as following.

 ________________________
|DATE           NEW   OLD|
|2016-01-21      2     1 |
|2016-01-22      1     1 |
|2016-01-24      1     1 |
|2016-01-25      1     0 |
|2016-01-26      0     1 |
|________________________|

I am currently getting results as following with this criteria.(Above table data was an example)

Criteria crit = sessionC.createCriteria(OP_DOCTOR_VISIT.class);
crit.add(Restrictions.ge("USER_DATETIME", fdate));
crit.add(Restrictions.le("USER_DATETIME", tdate));

ProjectionList p = Projections.projectionList();
p.add(Projections.sqlProjection("date(USER_DATETIME) as beginDate", new String[]{"beginDate"}, new Type[]{StandardBasicTypes.DATE}));
p.add(Projections.sqlProjection("date(USER_DATETIME) as endDate", new String[]{"endDate"}, new Type[]{StandardBasicTypes.DATE}));
p.add(Projections.groupProperty("FORM"));
p.add(Projections.count("FORM"));
p.add(Projections.alias(Projections.sqlGroupProjection("date(USER_DATETIME) as createdDate", "createdDate", new String[]{"createdDate"}, new Type[]{StandardBasicTypes.DATE}), "DAT"));


crit.addOrder(Order.asc("DAT"));
crit.setProjection(p);
List l = crit.list();


| 2016-01-25| 2016-01-25| NEW| 2 | 2016-01-25|
| 2016-01-25| 2016-01-25| OLD| 1 | 2016-01-25|
| 2016-01-27| 2016-01-27| NEW| 1 | 2016-01-27|
| 2016-01-27| 2016-01-27| OLD| 1 | 2016-01-27|
| 2016-01-28| 2016-01-28| NEW| 41| 2016-01-28|
| 2016-01-28| 2016-01-28| OLD| 1 | 2016-01-28|
| 2016-01-29| 2016-01-29| NEW| 2 | 2016-01-29|
| 2016-01-29| 2016-01-29| OLD| 10| 2016-01-29|

How can I make the date to be distinct and get the results of NEW and OLD in separate columns.

I have got it using sql query, can anyone help me convert it to hibernate criteria or query.

select count(ODV.FORM) - count(ODV1.OP_VISIT_ID) as 'OLD', count(ODV1.OP_VISIT_ID) as 'NEW' , 
DATE_FORMAT(ODV.USER_DATETIME,'%d-%m-%Y') as 'VISIT_DATE'
from OP_DOCTOR_VISIT ODV 
LEFT OUTER JOIN OP_DOCTOR_VISIT ODV1 on ODV.OP_VISIT_ID = ODV1.OP_VISIT_ID 
and (ODV1.FORM = 'NEW' and ODV1.USER_DATETIME >='2016-01-25' and ODV1.USER_DATETIME <='2016-01-30')

where (ODV.USER_DATETIME >='2016-01-25' and ODV.USER_DATETIME <='2016-01-30')
group by DATE_FORMAT(ODV.USER_DATETIME,'%d-%m-%Y')

Upvotes: 2

Views: 731

Answers (1)

Sanal S
Sanal S

Reputation: 1152

Found answer to my question: 

Criteria crit1 = sessionC.createCriteria(OP_DOCTOR_VISIT.class, "OPDV1");
crit.createAlias("OPDV1.OP_VISIT", "OPDV2", JoinType.LEFT_OUTER_JOIN,
Restrictions.and(Restrictions.eq("OPDV2.FORM", "NEW"), Restrictions.ge("OPDV2.USER_DATETIME", fdate), Restrictions.le("OPDV2.USER_DATETIME", tdate)));
    crit.add(Restrictions.ge("OPDV1.USER_DATETIME", fdate));
    crit.add(Restrictions.le("OPDV1.USER_DATETIME", tdate));
    ProjectionList p1 = Projections.projectionList();
    p1.add(Projections.alias(Projections.count("OPDV1.OP_VISIT_ID"), "TOTAL"));
    p1.add(Projections.count("OPDV2.FORM"));
    p1.add(Projections.alias(Projections.sqlGroupProjection("date(this_.USER_DATETIME) as createdDate", "createdDate", new String[]{"createdDate"}, new Type[]{StandardBasicTypes.DATE}), "DAT"));
    crit.setProjection(p1);
    return crit.list();

Upvotes: 1

Related Questions